[오라클] Lock 걸린 세션 조회 및 Lock 해제

SQLPLUS에서 실행해 보세요.
GUI환경에서는 아래 SQL를 굳이 사용할 일이 없겠죠. ^^

1.Locked Session 조회
-----------------------
SELECT LOCK_OBJ.OBJECT_ID
           , DBA_OBJ.OBJECT_NAME
           , DBA_OBJ.OBJECT_TYPE
           , LOCK_OBJ.SESSION_ID
           , DBA_OBJ.OWNER
           , LOCK_OBJ.XIDUSN
           , LOCK_OBJ.SESSION_ID
           , DECODE(LOCKED_MODE, 2, 'ROW SHARE',
                                                    3, 'ROW EXCLUSIVE', 
                                                    4, 'SHARE', 
                                                    5, 'SHARE ROW EXCLUSIVE',
                                                   6, 'EXCLUSIVE', 'UNKNOWN') LOCKED_MODE
FROM V$LOCKED_OBJECT LOCK_OBJ, DBA_OBJECTS DBA_OBJ
WHERE LOCK_OBJ.OBJECT_ID = DBA_OBJ.OBJECT_ID
    AND ORACLE_USERNAME = upper('&username');

2.Locked Session ID의 Session No 조회
-------------------------------------   
SELECT LOCK_OBJ.SESSION_ID
             ,SESN.SERIAL# AS SERIAL_NO
             ,LOCK_OBJ.OS_USER_NAME
             ,LOCK_OBJ.ORACLE_USERNAME
             ,SESN.STATUS
FROM V$LOCKED_OBJECT LOCK_OBJ, V$SESSION SESN
WHERE LOCK_OBJ.SESSION_ID = SESN.SID   
    AND LOCK_OBJ.SESSION_ID = '&ssid';
     
3. Lock Session Id, No로 lock session 죽이기
--------------------------------------------
ALTER SYSTEM KILL SESSION '&ssid, ssno';