"락 처리 절차"의 두 판 사이의 차이

1. lock 확인

-- lock 걸린 SERIAL_NO,SESSION_ID, OBJECT

SELECT DISTINCT t1.session_id AS session_id
               ,t2.serial# AS serial_no
               ,t1.os_user_name AS os_user_name
               ,t1.oracle_username AS oracle_username
               ,t2.status AS status
               ,t3.object_name
               ,DECODE( locked_mode
                       ,2, 'ROW SHARE'
                       ,3, 'ROW EXCLUSIVE'
                       ,4, 'SHARE'
                       ,5, 'SHARE ROW EXCLUSIVE'
                       ,6, 'EXCLUSIVE'
                       ,'UNKNOWN'
                      ) lock_mode
           FROM v$locked_object t1
              , v$session t2
              , dba_objects t3
          WHERE t1.session_id = t2.SID
            AND t1.object_id = t3.object_id;

SELECT S.USERNAME,
       L.SID,
       L.ID1,
       TRUNC(L.ID1 / POWER(2, 16)) RBS,
       BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
       L.ID2 SEQ,
       L.LMODE,
       L.REQUEST
FROM   V$LOCK    L,
       V$SESSION S
WHERE  L.SID      = S.SID
AND    L.TYPE     = 'TX'
AND    S.USERNAME = USER;

@ REQUEST 0 : 락을 보유 
@ REQUEST 6 : 락으로 인해 대기중인 상태

2. session_id를 *SESSION_ID*에 넣어 락 sql 찾기

-- lock 걸린 sql

SELECT   b.username username
        ,c.SID AS session_id
        ,c.owner object_owner
        ,c.OBJECT OBJECT
        ,b.lockwait
        ,a.sql_text SQL
        ,piece
    FROM v$sqltext a
       , v$session b
       , v$access c
   WHERE a.address = b.sql_address
     AND a.hash_value = b.sql_hash_value
     AND b.SID = c.SID
     AND c.owner != 'SYS'
     and c.SID = '&SESSION_ID'
ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;

3. 아래 쿼리 *SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.

-- lock 걸린 SESSION kill

alter system kill session 'SESSION_ID ,SERIAL_NO' ;

4. 3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다.

  * 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!

-- lock 걸린 PROCESS ID 찾기

SELECT DISTINCT s.username "ORACLE USER"
               ,p.pid "PROCESS ID"
               ,s.SID "SESSION ID"
               ,s.serial#
               ,osuser "OS USER"
               ,p.spid "PROC SPID"
               ,s.process "SESS SPID"
               ,s.lockwait "LOCK WAIT"
           FROM v$process p, v$session s, v$access a
          WHERE a.SID = s.SID
            AND p.addr = s.paddr
            AND s.username != 'SYS'

-- unix에 로그인 하여 process kill

kill -9 프로세스아이디

5. 락걸린 테이블 찾아서 kill

-- 1) 헤당 테이블 찾기  
SELECT * FROM v$lock
  WHERE id1 = (SELECT object_id 
                 FROM all_objects 
                WHERE owner ='오너명'
                  AND object_name ='테이블명'
               )
-- 2) 죽이기 
SELECT * FROM v$session WHERE sid = 찾은 sid값