ロックの解除を行う際に使うSQLのメモ

ロック解放のための道具箱 (Oracle)

ロック状態を解消するための作業に使用するSQLを，まとめました． ほとんど自分用のメモです．

↑

状況把握

↑

他のセッションを待たせているロックを表示する

ロックモード(SS/SX/S/SRX/X)に分けて表示します． 

/*
  他のセッションを待たせているロックを取得する
*/
select
  b.id1,
  a.sid,
  a.serial#,
  b.type,
  to_char(b.ctime / 60,'999999999.9') min,
  decode(b.lmode,
     1, 'NULL',
     2, '行共有(SS)',
     3, '行排他(SX)',
     4, '共有(S)',
     5, '共有行排他(SRX)',
     6, '排他(X)',
     '???') lmode,
  decode(b.request,
     1, 'NULL',
     2, '行共有(SS)',
     3, '行排他(SX)',
     4, '共有(S)',
     5, '共有行排他(SRX)',
     6, '排他(X)',
     '???') request
from v$session a, v$lock b
where a.sid = b.sid and
  (b.id1, b.id2) in (
   select d.id1,d.id2 from v$lock d
   where
    d.id1 = b.id1 and d.id2 = b.id2 and
    d.request > 0
   )
order by b.id1, b.ctime desc;
しかしこれだけで該当セッション(KILLするセッション)を絞るのは不安な場合， 以降のSQLも使って総合的に判断する．

↑

全ロックを表示する

/*
  全ロックを表示する (時間付き)
 */
select a.owner || '.' || a.object_name object,
  decode(b.locked_mode,
     1, 'NULL',
     2, '行共有(SS)',
     3, '行排他(SX)',
     4, '共有(S)',
     5, '共有行排他(SRX)',
     6, '排他(X)',
     '???' ) locked_mode,
  to_char(c.ctime / 60,'99990.9') min,
  b.oracle_username, b.os_user_name, b.process
from dba_objects a, v$locked_object b, v$lock c
where a.object_id = b.object_id and
  b.session_id = c.sid and
  b.xidsqn = c.id2 and
  b.xidusn > 0;
ロックをかけている時間(V$LOCK.CTIME)が表示されるので， この時間が異常に長いのは怪しいと考えられる．

↑

実行中のSQLを表示する

SQLを見て考える． 
/*
  実行中のSQLを取得する
*/
select
  username, osuser, machine, terminal, program,
  sid, serial#,
  status,
  sql_address, sql_text
from v$session s, v$sqltext q
where type = 'USER'
  and s.sql_address = q.address
order by s.sid, s.serial#, q.piece

↑

セッションのログイン時間を表示する

システムによっては，ログイン時間が古すぎるということで判断できる場合もあり得る． 
/*
  セッションの一覧を取得する (ログイン時間付き)
*/
select to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') logon_time2,
     username, osuser, machine, terminal, program,
     sid, serial#,
     status, sql_address
from v$session
where type = 'USER';

↑

該当セッションをKILLする

ロックしっぱなしのセッションを特定したら，ALTER SYSTEM KILL SESSIONを行う．

このSQLは，全セッションに対応して，KILLするためのSQL文群を作成するので， 対象セッションのSQLをコピぺして実行すれば良い． 

/*
  kill session用SQLを作成する
*/
select sid, serial#, username, machine, osuser, program, status,
    'alter system kill session ''' || sid || ',' || serial# || ''';' sql
from v$session
where username is not null;
まぁ，普通に該当セッションのSIDとSERIAL#を使って直書きしてもいいんですが， コピぺでできれば楽かなと．

© 2023 KMIソフトウェア