BLOCKING_SESSION_STATUS | VARCHAR2(11) | Blocking session status:
|
BLOCKING_INSTANCE | NUMBER | Instance identifier of blocking session |
BLOCKING_SESSION | NUMBER | Session identifier of blocking session |
可以直接找到阻塞 session,比昨天的使用v$lock来找更简单。SQL如下:
Select '节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||
' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||
b_s.SERIAL# blockinfo,
a_s.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
'后为被阻塞信息',
b_s.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.EVENT blocked_event,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID blocked_sql_id,
obj.owner blocked_owner,
obj.object_name blocked_object_name,
obj.OBJECT_TYPE blocked_OBJECT_TYPE,
case
when b_s.ROW_WAIT_OBJ# <> -1 then
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
else
'-1'
end blocked_rowid, --被阻塞数据的rowid
decode(obj.object_type,
'TABLE',
'select * from ' || obj.owner || '.' || obj.object_name ||
' where rowid=''' ||
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#) || '''',
NULL) blocked_data_querysql
from gv$session a_s, gv$session b_s, dba_objects obj
where b_s.BLOCKING_INSTANCE is not null
and b_s.BLOCKING_SESSION is not null
and a_s.INST_ID = b_s.BLOCKING_INSTANCE
and a_s.SID = b_s.BLOCKING_SESSION
and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
order by a_s.inst_id, a_s.sid
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-677572/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/195110/viewspace-677572/