[ORACLE] LOCK 확인 및 해제

 

뭐 될뻔한 경험에 의해 필요한 쿼리

 

업무에 참고하시기 바랍니다.!

 

반응형

1. LOCK 확인

SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;


OBJECT_ID	SESSION_ID	ORACLE_USERNAME		OS_USER_NAME
-----------------------------------------------------------------
163990		401		SCOTT			js
163966		401		SCOTT			js

 

2. LOCK 조금더 상세히 확인

SELECT A.SID
     , A.SERIAL#
     , object_name
     , A.SID || ', ' || A.SERIAL# AS KILL_TASK
  FROM V$SESSION A
 INNER JOIN V$LOCK B
    ON A.SID = B.SID
 INNER JOIN DBA_OBJECTS C
    ON B.ID1 = C.OBJECT_ID
 WHERE B.TYPE  = 'TM'
 ;
   

SID	SERIAL#	OBJECT_NAME	KILL_TASK
------------------------------------------------------------
401	12761	EMP		401, 12761
401	12761	EMP		401, 12761

 

3. sid 와 시리얼 번호로 세션 해제

 ALTER SYSTEM KILL SESSION '401, 12761';

 

4. LOCK 발생 사용자 및 개체 조회, 실행중이었던 sql lock 확인

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   ORDER BY T3.ADDRESS, T3.PIECE
   ;
   

SESSION_ID	SERIAL#	OBJECT_NAME	MACHINE	    	TERMINAL	PROGRAM		    	ADDRESS		PIECE	SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	0    	INSERT /*+ APPEND */ INTO EMP
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	1    	, EMPNO
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	2    	, EMPNM
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	3    	, DEPTNO