oracle不常用的查询语句

1.查看所有用户：select * from dba_user(all_users,user_users);
2.查看用户系统权限：select * from dba_sys_privs(all_sys_privs,user_sys_privs);
3.查看用户对象权限：select * from dba_tab_privs(all_tab_privs,user_tab_privs);
4.查看所有角色：select * from dba_roles;
5.查看用户所拥有的角色：select * from dba_role_privs(user_role_privs);
6.查询oracle中所有用户信息：select * from dba_user;
7.只查询用户和密码：select username,password from dba_users;
8.查询当前用户信息：select * from dba_ustats;
9.查询用户可以访问的视图文本：select * from dba_varrays;
10.查询数据库中所有视图的文本：select * from dba_views;
11.查询全部索引：select * from user_indexes;
12.查询全部表格：select * from user_tables;
13.查询全部约束：select * from user_constraints;
14.查询全部对象：select * from user_objects;
15.查看相关进程在数据库中的会话
Select a.sid,a.serial#,a.program, a.status,
substr(a.machine,1,20), a.terminal,b.spid
from v$session a, v$process b
where a.paddr=b.addr
and b.spid = &spid;
16.查看数据库中被锁住的对象和相关会话
select a.sid,a.serial#,a.username,a.program,
c.owner, c.object_name
from v$session a, v$locked_object b, all_objects c
where a.sid=b.session_id and
c.object_id = b.object_id;
17.查看相关会话正在执行的SQL
select sql_text from v$sqlarea where address =
( select sql_address from v$session where sid = &sid );
18.查看消耗资源最多的SQL
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000OR disk_reads > 1000000
ORDERBY buffer_gets + 100 * disk_reads DESC;
19.查看某条SQL语句的资源消耗：
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');
20.查询sql语句的动态执行计划:
首先使用下面的语句找到语句的在执行计划中的address和hash_code
SELECT sql_text, address, hash_value FROM v$sql t where (sql_text like '%FUNCTION_T(表名大写!)%')
然后:
SELECT operation, options, object_name, cost FROM v$sql_plan
WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;
21.查询oracle的版本:
select * from v$version;
22.查询数据库的一些参数:
select * from v$parameter
23.查找你的session信息
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
24.当machine已知的情况下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
25.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
select b.sql_text
from v$session a,v$sqlarea b
where a.sql_hashvalue=b.hash_value and a.sid=100