oracle 常用状态sql

--当前数据库运行状况：

select * from gv$lock

where

(type,id1,id2) in (select type,id1,id2 from gv$lock where request>0)

order by ctime desc,id1,id2,block desc;

select * from gv$locked_object ORDER BY object_id,inst_id,session_id;

select inst_id,event,wait_class,count(*) from gv$session group by inst_id,event,wait_class order by 1,4 desc;

--select * from v$session where type <> 'BACKGROUND' and status = 'ACTIVE';

SELECT * FROM v$log order by thread#,sequence#;

SELECT * FROM gv$resource_limit order by inst_id;

--统计数据库当前前台会话的操作类型及数量

select s.inst_id,s.command,a.name,s.num from (select inst_id,command,count(*) num from gv$session where  type <> 'BACKGROUND' group by inst_id,command) s,(select action,name from audit_actions) a  where a.action=s.command order by inst_id,num desc;

select t.inst_id,s.sid,s.username,s.osuser,t.START_TIME,d.timenow from gv$transaction t,gv$session s,(select to_char(sysdate,'MM/DD/YY HH24:MI:SS') timenow from dual) d where s.inst_id=t.inst_id and s.taddr= t.addr order by t.START_TIME;

--除后台会话以外的所有会话信息

SELECT inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT FROM gv$session where type <> 'BACKGROUND' order by status,event,seconds_in_wait desc;

--除后台会话以外的所有正在活动的会话信息

select inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT from gv$session where type <> 'BACKGROUND' and status = 'ACTIVE' order by status,event,seconds_in_wait desc;

--查某些特定等待事件的会话信息

select inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT from gv$session where event like '%dblink%' or event like '%db file%read%' or event like '%SQL*Net break/reset to client%' or event like '%read by other session%' order by status,event,seconds_in_wait desc;

--查某些特定会话号的会话信息

SELECT inst_id,SID,event,wait_class,username,type,osuser,status,SECONDS_IN_WAIT FROM gv$session WHERE SID IN (126,87) order by status,event,seconds_in_wait desc;

--查询正在执行的sql

select a.SID, a.USERNAME, s.SQL_TEXT

from gv$session a, gv$sqltext_with_newlines s

where a.INST_ID=s.INST_ID

and a.SQL_ADDRESS = s.ADDRESS

and a.SQL_HASH_VALUE = s.HASH_VALUE

and a.INST_ID=1

and a.SID in (126,87)

order by a.USERNAME, a.SID, s.PIECE;

--查询上一条执行过的sql

select a.SID, a.USERNAME, s.SQL_TEXT

from gv$session a, gv$sqltext_with_newlines s

where a.INST_ID=s.INST_ID

and a.PREV_SQL_ID = s.SQL_ID

and a.INST_ID=1

and a.SID in (126,87)

order by a.USERNAME, a.SID, s.PIECE;

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--查询引起cursor等待事件的会话sid

SELECT a.*, s.sql_text

  FROM v$sql s,

       (SELECT sid,

               event,

               wait_class,

               p1 cursor_hash_value,

               p2raw Mutex_value,

               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid

          FROM v$session_wait

         WHERE event LIKE 'cursor%') a

WHERE s.HASH_VALUE = a.cursor_hash_value;