Oracle10g中阻塞锁查询更简单

    今天发现Oracle10g(我的版本是：Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)中v$session增加了如下字段:

BLOCKING_SESSION_STATUS	VARCHAR2(11)	Blocking session status: VALID NO HOLDER GLOBAL NOT IN WAIT UNKNOWN
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/