Oracle 中常用查询用户、角色、权限等SQL

连带表间的外键关系一起删除所有某个用户下的所有表：
SELECT 'DROP TABLE '||TNAME||' CASCADE CONSTRAINT ;' FROM DBA_OBJECTS FROM DBA_OBJECTS WHERE OWNER='YOUR_USER' AND OBJECT_TYPE='TABLE'
然后将选出的结果粘贴到SQLPLUS里执行

1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs; (查看当前用户所拥有的权限)

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

5.查看所有角色： select * from dba_roles;

6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS

8.SqlPlus中查看一个用户所拥有权限
SQL>select * from dba_sys_privs where grantee='username'; 其中的username即用户名要大写才行。
比如： SQL>select * from dba_sys_privs where grantee='TOM';

9、Oracle删除指定用户所有表的方法
select 'Drop table '||table_name||';' from all_tables where owner='要删除的用户名(注意要大写)';

10、删除用户
drop user user_name cascade; 如：drop user SMCHANNEL CASCADE

11、获取当前用户下所有的表：
select table_name from user_tables;

12、删除某用户下所有的表数据:
select 'truncate table ' || table_name from user_tables;

13、禁止外键 ORACLE数据库中的外键约束名都在表user_constraints中可以查到。
其中constraint_type='R'表示是外键约束。

启用外键约束的命令为：alter table table_name enable constraint constraint_name
禁用外键约束的命令为：alter table table_name disable constraint constraint_name

然后再用SQL查出数据库中所以外键的约束名：
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

14、ORACLE禁用/启用外键和触发器 --启用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints
where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
commit;

--禁用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints
where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
commit;

15、当前连接数
select count(*) from v$process ;

16、最大连接数
select value from v$parameter where name ='processes';

17、当前所有用户，按照连接数排序
select username,count(username) as con from v$session where username is not null group by username order by con desc;

18、按执行时间排序
SELECT osuser,
       a.username,
       cpu_time / executions / 1000000 || 's',
       sql_fulltext,
       machine
  from v$session a, v$sqlarea b
 where a.sql_address = b.address
 order by cpu_time / executions desc;

19、查看用户和默认表空间的关系。
select   username,default_tablespace   from   dba_users;

1.查看表结构：desc表名

2.查看当前用户的表：
select  table_name from user_tables;

3.查看所有用户的表名：
select   table_name  from all_tables;

4.查看所有表名(其中包括系统表)
select  table_name  from all_tables;

5.查看所有的表:
select  *  from  tab/dba_tables/dba_objects/cat;

下面介绍Oracle查询用户表空间
Oracle查询用户表空间：select * fromuser_all_tables
Oracle查询所有函数和储存过程：select * fromuser_source
Oracle查询所有用户：select * fromall_users.select * from dba_users
Oracle查看当前用户连接：select * fromv$Session
Oracle查看当前用户权限：select * fromsession_privs
Oracle查看用户表空间使用情况：
select a.file_id "FileNo",a.tablespace_name
"Tablespace_name",
a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",
sum(nvl(b.bytes,0)) "Free",
sum(nvl(b.bytes,0))/a.bytes*100 "%free"
from dba_data_files a, dba_free_space b
where  a.file_id  = b  .file_id(+)
group by a.tablespace_name ,
a.file_id,a.bytes order by a.tablespace_name;
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.tablespace_name='HRSM'
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name

1、查看所有用户：
select * from dba_user;
select * from all_users;
select * from user_users;

2、查看用户系统权限：
select * from dba_sys_privs;
select * from all_sys_privs;
select * from user_sys_privs;

3、查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

4、查看所有角色：
select * from dba_roles;

5、查看用户所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

6、查看角色所拥有的权限:
select * from role_sys_privs;
select * from role_tab_privs;

7、查看所有系统权限
select * from system_privilege_map;

8、查看所有对象权限
select * from table_privilege_map;

在DB2中为：
select * from syscat.dbauth

查看sid
select * from v$instance

9、Oracle查看占用表空间最大的表（排序）
select t.owner,
       t.segment_name,
       t.tablespace_name,
       bytes / 1024 / 1024 / 1024 as sizes,
       q.num_rows,
       t.segment_type
  from dba_segments t
  left join dba_tables q
    on t.segment_name = q.table_name
   and t.owner = q.owner
 where t.segment_type = 'TABLE'
   and t.tablespace_name = 'xxx_TABLESPACE' --需要查看的表空间
   and t.owner = 'xxx'
 order by 4 desc

10、Oracle查看表的创建时间和最后修改时间
SELECT CREATED,LAST_DDL_TIME from user_objects where object_name=upper('表名');
CREATED 为创建时间
LAST_DDL_TIME为最后修改时间

11、Oracle中查询某个字段新增的时间
select table_name,column_name,data_type,data_length,last_analyzed from user_tab_columns where column_name='字段名'and table_name='表名'

12、Oracle查看数据库中表的创建时间
create table test(id int);
select created,last_ddl_time from user_objects where object_name = 'TEST';
select to_char(created, 'HH24:MI:SS') "Created" , to_char(last_ddl_time, 'HH24:MI:SS') "DDL" from user_objects where object_name = 'TEST';
select * from user_objects where object_name = 'TEST';

oracle创建用户需要什么权限,Oracle 创建普通用户，并赋予权限
采用sys or system / manager assysdba; 连接数据库。
创建普通用户oadmin: create user oadmin identified by pwd_oracle;

删除用户：
drop user oadmin;

授予用户登录数据库的权限：
grant create session to oadmin;

授予用户操作表空间的权限：
grant unlimited tablespace to oadmin;
grant create tablespace to oadmin;
grant alter tablespace to oadmin;
grant drop tablespace to oadmin;
grant manage tablespace to oadmin;

授予用户操作表的权限：
grant create table to oadmin; (包含有create index权限, alter table, drop table权限)

授予用户操作视图的权限:
grant create view to oadmin; (包含有alter view, drop view权限)

授予用户操作触发器的权限：
grant create trigger to oadmin; (包含有alter trigger, drop trigger权限)

授予用户操作存储过程的权限：
grant create procedure to oadmin;(包含有alter procedure, drop procedure 和function 以及 package权限)

授予用户操作序列的权限：
grant create sequence to oadmin; (包含有创建、修改、删除以及选择序列)

授予用户回退段权限：
grant create rollback segment to oadmin;
grant alter rollback segment to oadmin;
grant drop rollback segment to oadmin;

授予用户同义词权限：
grant create synonym to oadmin;(包含drop synonym权限)
grant createpublicsynonym to oadmin;
grant droppublicsynonym to oadmin;

授予用户关于用户的权限：
grant create user to oadmin;
grant alter user to oadmin;
grant become user to oadmin;
grant drop user to oadmin;

授予用户关于角色的权限：
grant create role to oadmin;

授予用户操作概要文件的权限
grant create profile to oadmin;
grant alter profile to oadmin;
grant drop profile to oadmin;

允许从sys用户所拥有的数据字典表中进行选择
grant select any dictionary to oadmin;