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