查看会话、锁、等待事件

1. 查看活动会话信息

SELECT *
  FROM V$SESSION
 WHERE USERNAME IS NOT NULL
   AND STATUS = 'ACTIVE'
 ORDER BY LOGON_TIME, SID;

       Active：正执行sql语句(waiting for/using a resource)

       Inactive：等待操作(即等待需要执行的sql语句)

       killed：被标记为删除

2. 查看当前会话执行的sql语句及进程信息

SELECT P.SPID,
       S.SID,
       S.OSUSER,
       S.MACHINE,
       S.PROGRAM,
       S.LOGON_TIME,
       S.SERIAL#,
       Q.MODULE,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM V$SESSION S, V$PROCESS P, V$SQLAREA Q
 WHERE S.PADDR = P.ADDR(+)
   AND S.SQL_ID = Q.SQL_ID(+)
   AND S.AUDSID = USERENV('SESSIONID');

else:获取当前会话的SID

SELECT * FROM V$SESSION  WHERE AUDSID = USERENV('SESSIONID');

SELECT * FROM V$MYSTAT A WHERE ROWNUM=1;

SELECT USERENV('SID') FROM DUAL;

3. 查询会话等待与其对应的sql

SELECT P.PID,
       S.SID,
       S.SERIAL#,
       S.USERNAME,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT,
       W.EVENT,
       W.WAIT_TIME,
       W.STATE,
       CASE
         WHEN W.STATE = 'WAITING' THEN
          W.SECONDS_IN_WAIT
         WHEN W.STATE = 'WAITING KNOWN TIME' THEN
          W.WAIT_TIME
       END AS SEC_IN_WAIT
  FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
 WHERE S.SID = W.SID
   AND S.SQL_ID = Q.SQL_ID
   AND P.ADDR = S.PADDR
   AND W.EVENT NOT LIKE 'SQL*Net%'
   AND S.USERNAME IS NOT NULL
   AND W.WAIT_TIME >= 0
 ORDER BY W.SECONDS_IN_WAIT DESC;

         如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。

         如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。

         如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。

4. 查询会话等待事件及对应的sql

SELECT P.PID,
       S.SID,
       S.SERIAL#,
       S.USERNAME,
       Q.SQL_ID,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT,
       E.EVENT,
       E.TOTAL_WAITS,
       E.TIME_WAITED,
       E.AVERAGE_WAIT
  FROM V$SESSION S, V$SESSION_EVENT E, V$SQLAREA Q, V$PROCESS P
 WHERE S.SID = E.SID
   AND S.SQL_ID = Q.SQL_ID
   AND P.ADDR = S.PADDR
   AND E.EVENT NOT LIKE 'SQL*Net%'
   AND S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL;

5. 查询长时间运行的会话与对应的sql

SELECT S.SID,
       OPNAME,
       TRUNC(L.SOFAR / L.TOTALWORK * 100, 2) || '%' AS PCT_WORK,
       L.ELAPSED_SECONDS ELAPSED,
       ROUND(L.ELAPSED_SECONDS * (L.TOTALWORK - L.SOFAR) / L.SOFAR) REMAIN_TIME,
       Q.SQL_TEXT
  FROM V$SESSION_LONGOPS L, V$SQLAREA Q, V$SESSION S
 WHERE L.SQL_HASH_VALUE = Q.HASH_VALUE
   AND L.SID = S.SID
   --AND L.SOFAR != L.TOTALWORK
   AND L.ELAPSED_SECONDS>6
 ORDER BY L.START_TIME

6. 查询会话及锁与对应的sql

SELECT A.OWNER 方案名,
       A.OBJECT_NAME 表名,
       B.XIDUSN 回滚段号,
       B.XIDSLOT 槽号,
       B.XIDSQN 序列号,
       B.SESSION_ID 锁表SESSION_ID,
       B.ORACLE_USERNAME 锁表用户名,
       DECODE(D.TYPE,
              'XR',
              'NULL',
              'RS',
              'SS(Row-S)',
              'CF',
              'SS(Row-S)',
              'TM',
              'TABLE LOCK',
              'PW',
              'TABLE LOCK',
              'TO',
              'TABLE LOCK',
              'TS',
              'TABLE LOCK',
              'RT',
              'ROW LOCK',
              'TX',
              'ROW LOCK',
              'MR',
              'S(Share)',
              NULL) 锁定方式,
       C.MACHINE 用户组,
       C.TERMINAL 机器名,
       B.OS_USER_NAME 系统用户名,
       B.PROCESS 系统进程ID,
       DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
       C.SERVER,
       C.SID,
       E.SQL_TEXT,
       C.SERIAL#,
       C.PROGRAM 连接方式,
       C.LOGON_TIME
  FROM ALL_OBJECTS     A,
       V$LOCKED_OBJECT B,
       SYS.GV_$SESSION C,
       V$LOCK          D,
       V$SQLTEXT       E
 WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS)
   AND C.SID = D.SID
   AND B.LOCKED_MODE = D.LMODE
   AND C.SQL_ID = E.SQL_ID(+)
 ORDER BY 1, 2;

7. 查询被阻塞会话与被阻塞会话的对应sql

SELECT S1.USERNAME "WAITING USER",
       S1.OSUSER   "OS User"
       
       ,
       S1.LOGON_TIME "logon time",
       W.SESSION_ID  "Sid",
       P1.SPID       "PID",
       Q1.SQL_TEXT   "SQLTEXT",
       S2.USERNAME   "HOLDING User",
       S2.OSUSER     "OS User"
       
       ,
       S2.LOGON_TIME "logon time",
       H.SESSION_ID  "Sid",
       P2.SPID       "PID",
       Q2.SQL_TEXT   "SQLTEXT"
  FROM SYS.V_$PROCESS P1,
       SYS.V_$PROCESS P2,
       SYS.V_$SESSION S1,
       SYS.V_$SESSION S2,
       DBA_LOCKS      W,
       DBA_LOCKS      H,
       V$SQL          Q1,
       V$SQL          Q2
 WHERE H.MODE_HELD != 'None'
   AND H.MODE_HELD != 'Null'
   AND W.MODE_REQUESTED != 'None'
   AND W.LOCK_TYPE(+) = H.LOCK_TYPE
   AND W.LOCK_ID1(+) = H.LOCK_ID1
   AND W.LOCK_ID2(+) = H.LOCK_ID2
   AND W.SESSION_ID = S1.SID(+)
   AND H.SESSION_ID = S2.SID(+)
   AND S1.PADDR = P1.ADDR(+)
   AND S2.PADDR = P2.ADDR(+)
   AND S1.SQL_ID = Q1.SQL_ID(+)
   AND S2.SQL_ID = Q2.SQL_ID(+)
 ORDER BY H.SESSION_ID;

                   $ORACLE_HOME/rdbms/admin/catblock.sql
                   $ORACLE_HOME/rdbms/admin/utllockt.sql