Oracle　行ロックで待たされているSQLと対象レコードを特定する

DBMS_ROWID.ROWID_CREATE()でROWIDを作成する

V$SESSIONのBLOCKING_SESSION_STATUSを見れば行ロックが原因で待たされているSQLと対象レコードの特定ができます。レコードの特定はDBMS_ROWID.ROWID_CREATE()ファンクションを使うとrowidを求める事ができるのでこちらを使います。

ロックで待たされているセッションのSQLとその対象行を特定するSQL

select
 s.INST_ID
,s.SID
,s.SERIAL#
-- ,s.USERNAME
-- ,s.STATUS
,s.SQL_ID
,o.OWNER
,o.OBJECT_NAME
,DBMS_ROWID.ROWID_CREATE (
  rowid_type    =>  1 
 ,object_number => s.ROW_WAIT_OBJ#
 ,relative_fno  => s.ROW_WAIT_FILE#
 ,block_number  => s.ROW_WAIT_BLOCK#
 ,row_number    => s.ROW_WAIT_ROW#
 ) as M_ROWID -- rowidを導出
,decode(l.LOCKED_MODE
        ,0, 'ロック要求中'     -- まだ取得できていない
        ,1, 'NULL' 
        ,2, '行共有(SS)'       -- 行共有ロック
        ,3, '行排他(SX)'       -- 行排他表ロック
        ,4, '共有(S)'          -- 共有表ロック
        ,5, '共有行排他(SRX)'  -- 共有行排他表ロック
        ,6, '排他(X)'          -- 排他表ロック
        ,'???' ) locked_mode_dec
from GV$SESSION s inner join GV$LOCKED_OBJECT l 
on  s.INST_ID = l.INST_ID and s.sid = l.session_id
inner join DBA_OBJECTS o
on l.object_id = o.object_id
where s.blocking_session_status ='VALID'
order by s.SID
;

出力結果例

INST_ID    SID    SERIAL# SQL_ID        OWNER      OBJECT_NAM M_ROWID              LOCKED_MOD
------- ------ ---------- ------------- ---------- ---------- -------------------- ----------
      1    261      45418 116009648rnhr SOONI      EX01       AAAYbdAAFAAAADVAAF   行排他(SX)

SQL>
--
-- DBMS_ROWID.ROWID_CREATE()で求めたROWIDで対象行を特定する
--
SQL> select * from sooni.ex01 where rowid='AAAYbdAAFAAAADVAAF'
  2  ;

   SID    SNAME    BIRTHDAY       NOTE    KANA    ROMA
______ ________ ___________ __________ _______ _______
     7 喜多川      00-12-13    納豆は嫌いです

SQL>
--
-- SQL_IDをキーにV$SQLから対象（待たされている）SQLを確認する
--
SQL> select s.SQL_TEXT from v$sql s where s.SQL_ID='116009648rnhr'
  2  ;

SQL_TEXT
----------------------------------------------------------------------------------
update ex01 t set t.SNAME='木田川' where t.SID=7

SQL>

SQL＊Plus用

set lin 100
COLUMN INST_ID FORMAT 99
COLUMN SID FORMAT 99999
COLUMN SQL_ID FORMAT A13
COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A10
COLUMN M_ROWID FORMAT A20
COLUMN locked_mode_dec FORMAT A10