[oracle] lock 확인 및 제거

oracle lock 확인 중 검색된 정보 입니다.

 

출처

http://blog.naver.com/motbombe/90028439497

 

 

락걸린 테이블 확인

SELECT do.object_name, do.owner, do.object_type, do.owner
             , vo.xidusn, vo.session_id, vo.locked_mode
  FROM v$locked_object vo , dba_objects do
 WHERE vo.object_id = do.object_id ;

 

해당 테이블이 락에 걸렸는지 확인

SELECT a.sid, a.serial#, b.type, c.object_name
  FROM v$session a, v$lock b, dba_objects c
 WHERE a.sid=b.sid 
   AND b.id1=c.object_id
   AND b.type='TM' 
   AND c.object_name IN ('테이블명');

 

락발생 사용자와 sql, object 조회

SELECT DISTINCT x.session_id, a.serial#, d.object_name, a.machine
            , a.terminal, a.program, b.address, b.piece, b.sql_text
  FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d
 WHERE x.session_id = a.sid 
   AND x.object_id = d.object_id 
   AND a.sql_address = b.address 
 ORDER BY b.address,b.piece;

 

락 발생 사용자확인

SELECT DISTINCT x.session_id, a.serial#, d.object_name, a.machine
             , a.terminal, a.program, a.logon_time 
             , 'alter system kill session ''' || a.sid || ', ' || a.serial# || ''';'
  FROM gv$locked_object x, gv$session a, dba_objects d
 WHERE x.session_id = a.sid and x.object_id = d.object_id 
 ORDER BY logon_time; 

 

접속 사용자 제거

--alter system kill session 'session_id,serial#';
ALTER SYSTEM KILL SESSION '26,6044'; 

 

현재 접속자의 sql 분석

SELECT DISTINCT a.sid, a.serial#, a.machine, a.terminal, a.program
             , b.address, b.piece, b.sql_text
  FROM v$session a, v$sqltext b
 WHERE a.sql_address = b.address 
 ORDER BY a.sid, a.serial#,b.address,b.piece;