oracle9i阻塞,Oracle10g中阻塞锁查询更简单

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