Oracle常用SQL命令整理

才疏学浅，欢迎批评指正

主要整理了Oracle常用的SQL命令

1.关于用户

• 查看当前用户权限
  select * from session_privs;

• 使用DBA查看所用户权限
  select * from dba_role_privs;

• 查看具有DBA权限的用户
  select * from dba_role_privs where granted_role='DBA';

• 查询所有用户
  select * from dba_users;
  或
  select * from all_users;

• 建立用户
  create user 用户名 identified by 密码;

• 给用户授权

grant create session to 用户名;
grant create table to  用户名;
grant create tablespace to  用户名;
grant create view to  用户名;

• 删除用户
  drop user 用户名 //仅删除用户

  drop user 用户名 cascade //删除用户以及用户创建的东西

2.关于表空间

• 查询当前用户的所属表空间
  select * from user_users;

• 查询表空间详情
  select * from dba_data_files;

• 创建表空间
  create tablespace 表空间名 datafile '表空间文件位置' size 1500M autoextend on; //指定表空间名称和对应的数据文件，默认表空间大小1500M，表空间自增开启

• 增加表空间大小
  alter database datafile '表空间文件位置' autoextend on; //打开自增长

  alter database datafile '表空间文件位置' autoextend on next 100M; //打开自增长，每次自增长100M

  alter database datafile '表空间文件位置' autoextend on next 100M maxsize 1024M; //打开自增长，每次增长100M，最大不超过1024M

• 删除表空间
  drop tablespace 表空间名 including contents and datafiles;

• 授予用户使用表空间的权限：
  alter user 用户名 quota unlimited on 表空间名;
  或
  alter user 用户名 quota *M on 表空间名;

• 查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; 
SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name; 

3.关于表

• 创建表
  create table 表名;
• 删除表
  drop table 表名;
• 查看一张表的大小
  SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name='表名';
• 查询所有表的表名
  select * from user_tables;
• 查询所有表的记录数量
  select t.table_name,t.num_rows from user_tables t;

4.sequence

• 获得所有sequence的创建语句
  select 'create sequence ' ||sequence_name|| ' minvalue ' ||min_value|| ' maxvalue ' ||max_value|| ' start with ' ||last_number|| ' increment by ' ||increment_by|| ( case when cache_size= 0 then ' nocache' else ' cache ' ||cache_size end) || ';' from user_sequences;

5.约束

• 获得所有约束的关闭语句
  select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';' from user_constraints;
• 获得所有约束的开启语句
  select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';' from user_constraints;

6.trigger

• 获得所有触发器关闭的语句
  select 'alter trigger '||trigger_name||' disable;' from user_triggers;
• 获得所有触发器开启的语句
  select 'alter trigger '||trigger_name||' enable;' from user_triggers;

7.创建路径

• 创建文件夹
  Create or replace directory 文件夹名 as ’存放路径’;
• 授权给使用的用户
  Grant read,write to 用户名;

8.数据泵

• 给使用expdp的用户授权（exp_full_database）
  grant exp_full_database to 用户名;
• 给使用impdp的用户授权（imp_full_database）
  grant imp_full_database to 用户名;
  （EXP_FULL_DATABASE， IMP_FULL_DATABASE：这两个角色用于数据导入导出工具的使用）
• 查看数据泵任务
  select * from dba_datapump_jobs;
• 中断数据泵任务（ 在命令行下按）
  ctrl+c
• 恢复数据泵任务（命令行下输入 ）
  expdp ATTACH=expdptable1
  impdp ATTACH=impdptable1
  继续expdptable1或impdptable1作业
  expdp/ impdp ATTACH=作业名

8.DB_link

• 创建DB_link
  grant create (public) database link to 用户名;
• 放弃DB_link
  drop (public) database link 连接名
• 查看所有连接
  select * from dba_db_links;