락 걸린 테이블 확인 및 락 해제

-- 락 걸린 테이블 확인
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 (${위 쿼리에서 가져온 OBJECT_NAME});


-- 락 발생 사용자, 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;


-- 현재 접속자의 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;


-- 락 세션 죽이기위한 sid 와 serial
SELECT
    A.SID
  , A.SERIAL#
  , A.SID || ', ' || A.SERIAL#
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 ('${락 걸린 테이블 명}');


-- 해당 세선 kill
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
;


-- 최근 실행된 쿼리 찾기
SELECT 
    SQL_FULLTEXT
  , SQL_ID
  , PARSING_SCHEMA_NAME
  , SERVICE
  , MODULE
  , FIRST_LOAD_TIME
  , LAST_LOAD_TIME
FROM V$SQL
WHERE 1 = 1
  AND PARSING_SCHEMA_NAME = '${스키마 명}'
  AND SERVICE = '${서비스 명}'
ORDER BY LAST_LOAD_TIME DESC
;


-- 최근 실행된 쿼리에 바인팅 변수 찾기
SELECT
    NAME
  , POSITION
  , DATATYPE_STRING
  , VALUE_STRING 
FROM V$SQL_BIND_CAPTURE
WHERE 1=1
  AND SQL_ID = '${위 쿼리에서 가져온 SQL_ID}'
;

SELECT * FROM V$SQL;

SELECT 
  last_active_time
  ,parsing_schema_name
  ,sql_text 
FROM v$sqlarea
WHERE 
  parsing_schema_name <> 'SYS'
  AND parsing_schema_name <> 'SYSMAN'
  AND parsing_schema_name <> 'DBSNMP'
  AND parsing_schema_name <> 'MDSYS'
  AND parsing_schema_name <> 'EXFSYS'
ORDER BY 
  last_active_time DESC;

 

 

안타깝게도 어떤 쿼리가 락을 걸었는지는 알기 쉽지 않다.

특히나 Oracle DB S/W update를 주기적으로 하지 않는다면 유지보수가 쉽지 않다.