Oracle Table Lock

1. 락 테이블 확인 

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;

2. 락 테이블 kill

SELECT 

    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 = 'TB_CO_GENO'

     

SID SERIAL#

--- -------

5   1

6   1

*ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

SQL> alter system kill session '5, 1';

SQL> alter system kill session '6, 1';

3. 락 세션 kill sql문

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;