- SELECT *
- FROM V$SESSION
- WHERE USERNAME IS NOT NULL
- AND STATUS = 'ACTIVE'
- ORDER BY LOGON_TIME, SID;
2. 查询会话与对应的进程
- SELECT P.SPID,
- S.SID,
- S.OSUSER,
- S.MACHINE,
- S.PROGRAM,
- S.LOGON_TIME,
- Q.MODULE,
- S.SERIAL#,
- Q.SQL_ID,
- Q.SQL_TEXT,
- Q.SQL_FULLTEXT
- FROM V$PROCESS P
- INNER JOIN V$SESSION S
- ON P.ADDR = S.PADDR
- INNER JOIN V$SQLAREA Q
- ON S.SQL_ID = Q.SQL_ID
- WHERE S.AUDSID = USERENV('SESSIONID');
-- 查询当前会话
2. 查询会话与对应的进程
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
- ;
3. 查询会话等待与对应的 SQL
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
- ;
4. 查询会话等待事件与对应 SQL
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
- ;
5. 查询长时间运行的会话与对应的 SQL
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;
6. 查询会话及锁与对应的 SQL
- select t2.username,
- t2.sid,
- t2.serial#,
- t3.object_name,
- t2.OSUSER,
- t2.MACHINE,
- t2.PROGRAM,
- t2.LOGON_TIME,
- t2.COMMAND,
- t2.LOCKWAIT,
- t2.SADDR,
- t2.PADDR,
- t2.TADDR,
- t2.SQL_ADDRESS,
- t1.LOCKED_MODE
- from v$locked_object t1, v$session t2, dba_objects t3,v$sql t4
- where t1.session_id = t2.sid
- and t1.object_id = t3.object_id
- --- and t2.SADDR=t4.ADDRESS(+)
- and t2.sql_id=t4.sql_id
- order by t2.logon_time
- alter system kill session '139, 182'
- : sid 182 : sertal#
6.1 查询会话及锁与对应 SQL
6.1 查询会话及锁与对应 SQL
6.2 查询阻塞会话与被阻塞会话的对应 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
- ;
6.2 查询阻塞会话与被阻塞会话的对应 SQL
来源: http://www.bubuko.com/infodetail-3366002.html