[오라클] 락 정보 조회 쿼리 및 락 세션 KILL

1. 락 정보 조회 쿼리
  1.1 SQL 정보 제외
SELECT                                              
  VLO.OBJECT_ID                                 
, VLO.SESSION_ID                                
, VS.SERIAL#                                    
, VLO.SESSION_ID || ',' || VS.SERIAL# AS LOCK_ID
, VLO.ORACLE_USERNAME                           
, VLO.OS_USER_NAME                              
, DO.OBJECT_NAME                                
, VS.PROGRAM
, VS.STATE
, VS.STATIS
, DO.OBJECT_TYPE                                
, DECODE(VLO.LOCKED_MODE,
          1,'NULL',
          2,'ROW-SHARE',
          3,'ROW-EXCLUSIVE',
          4,'SHARE',
          5,'SHARE-ROW-EXCLU',
          6,'EXCLU') AS LOCK_MODE
, VS.SQL_ID                                     
FROM V$LOCKED_OBJECT VLO                        
   , DBA_OBJECTS DO                             
   , V$SESSION VS                               
WHERE VLO.OBJECT_ID = DO.OBJECT_ID              
  AND VLO.SESSION_ID = VS.SID ;

  1.2 SQL 정보 포함
SELECT                                               
  VLO.SESSION_ID AS SID                                 
, VS.SERIAL#
, VS.MACHINE
, VSQL.SQL_FULLTEXT
, DO.OBJECT_NAME                                 
, VLO.ORACLE_USERNAME AS ORA_USR                            
, VLO.OS_USER_NAME AS OS_USR                               
, VLO.SESSION_ID || ',' || VS.SERIAL# AS LOCK_ID 
, VLO.OBJECT_ID AS OBJ_ID                                  
, VS.PROGRAM 
, VS.STATE 
, DO.OBJECT_TYPE                                 
, DECODE(VLO.LOCKED_MODE, 
          1,'NULL', 
          2,'ROW-SHARE', 
          3,'ROW-EXCLUSIVE', 
          4,'SHARE', 
          5,'SHARE-ROW-EXCLU', 
          6,'EXCLU') AS LOCK_MODE 
, VS.SQL_ID
, 'ALTER SYSTEM KILL SESSION ''' || VLO.SESSION_ID || ', ' || VS.SERIAL# || '''' AS KILL_CMD
FROM V$LOCKED_OBJECT VLO                         
   LEFT OUTER JOIN DBA_OBJECTS DO ON VLO.OBJECT_ID = DO.OBJECT_ID                               
   LEFT OUTER JOIN V$SESSION VS ON VLO.SESSION_ID = VS.SID
   LEFT OUTER JOIN V$SQL VSQL ON VS.SQL_ID = VSQL.SQL_ID; 

2. 락 세션 KILL 
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
  
3. 참고
  V$SESSION 테이블의 STATUS 필드는 현재 세션의 상태를 보유 하고 있다. 
    ACTIVE : SQL문을 수행하고 있을 때  
    INACTIVE : 유휴 상태일 때
    KILLED : Inactive 세션을 Kill 했을 때 나타는 상태  
    CACHED: Oracle*XA에 의해 임시로 캐시될 때
    SNIPED : 세션은 Inactive이고 클라이언트를 기다릴 때