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
