什么是oracle视图脚本,Oracle 常用的V$ 视图脚本使用基础教程(3)

22 检查角色和权限设置

根据用户名进行授权的对象级特权

select b.owner || '.' || b.table_name obj,b.privilege what_granted, b.grantable,a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1,2,3;

根据被授权人进行授权的对象级特权

Select owner || '.' || table_name obj,privilege what_granted, grantable, grantee from sys.dba_tab_privs where not exists(select 'x'from sys.dba_users where username = grantee)order by 1,2,3;

根据用户名进行授予的系统级特权

select b.privilege what_granted,b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1,2;

根据被授权人进行授予的系统级特权

select privilege what_granted,admin_option, grantee from sys.dba_sys_privs where not exists ( select 'x' from sys.dba_users where username = grantee ) order by 1,2;

根据用户名授予的角色

select b.granted_role ||decode(admin_option, 'YES',' (With Admin Option)',null) what_granted, a.username from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;

根据被授权人授予的角色

select granted_role ||decode(admin_option, 'YES',' (With Admin Option)', null) what_granted,grantee from sys.dba_role_privs where not exists(select 'x'from sys.dba_users where username = grantee ) order by 1;

用户名及已被授予的相应权限

select a.username,b.granted_role || decode(admin_option,'YES',' (With Admin Option)',null) what_granted from

sys.dba_users a,sys.dba_role_privs b where a.username = b.grantee

UNION

select a.username,b.privilege || decode(admin_option,'YES',' (With Admin Option)', null) what_granted from

sys.dba_users a,sys.dba_sys_privs b where a.username = b.grantee

UNION

select a.username,b.table_name ||'-' || b.privilege|| decode(grantable,'YES',' (With Grant Option)',null)

what_granted from sys.dba_users a, sys.dba_tab_privs b where a.username = b.granteeorder by 1;

查询用户名及相应的配置文件、默认的表空间和临时表空间

Select username, profile, default_tablespace,temporary_tablespace, created from sys.dba_users order by username;

23.等待事件V$视图

在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。

马上该谁等待--查询V$SESSION_WAIT / V$SESSION

select event, sum(decode(wait_time,0,1,0)) "Waiting Now",sum(decode(wait_time,0,0,1)) "Previous Waits",count(*) "Total" from v$session_wait group by event order by count(*);

马上该谁等待；SPECIFIC Waits--查询V$SESSION_WAIT

SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

谁在等待 - 最后10 个等待数--查询V$SESSION_WAIT_HISTORY

SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

查找P1, P2, P3代表什么--查询 V$EVENT_NAME

select event#,name,parameter1 p1,parameter2 p2,parameter3 p3 from v$event_name where name in ('buffer busy waits', 'write complete waits');

会话开始后的所有等待数--查询 V$SESSION_EVENT

select sid, event, total_waits, time_waited, event_id from v$session_event where time_waited > 0 order by time_waited;

类的所有会话等待数--查询V$SESSION_WAIT_CLASS

select sid, wait_class, total_waits from v$session_wait_class;

系统启动后的所有等待数--查询V$SYSTEM_EVENT

select event, total_waits, time_waited, event_id from v$system_event where time_waited > 0 order by time_waited;

类的系统等待数--查询V$SYSTEM_WAIT_CLASS

select wait_class, total_waits from v$system_wait_class order by total_waits desc;

类的系统等待数--查询V$ACTIVE_SESSION_HISTORY

--In the query below, the highest count session is leader in non-idle wait events.

select session_id,count(1) from v$active_session_history group by session_id order by 2;

--In the query below, find the SQL for the leader in non-idle wait events.

select c.sql_id, a.sql_text from v$sql a, (select sql_id,count(1) from v$active_session_history b where sql_id is not null group by sql_idorder by 2 desc ) c where rownum <= 5 order by rownum;