[Oracle] ORA-00054 & ORA-00031오류 해결 방법(lock해제)

ORA-00054 & ORA-00031

ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다.

라는 오류로 인해 곤란을 겪으실 분들을 위해 간단한 해결 방법 작성해드리려고 합니다.

 

ORA-00031:  session marked for kill

 

위 두개의 오류는 테이블이 락이 걸려서 TOAD의 사용이 막힌 경우이며 SYSTEM 계정으로 접근하여 아래 쿼리를 입력하면 테이블의 락이 해제됩니다.

 

테이블 lock 조회 방법에 대해 몇가지 살펴보겠습니다.

방법1 : 해당 테이블이 lock이 걸렸는지 확인하는 방법 

저는 이 방법으로 lock이 걸린 sid를 확인하였습니다.

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='**테이블명**'

방법2 : lock 걸린 세션 확인 

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';

방법3 :  lock 걸려있는 테이블 갯수 확인

select count(*)
from v$locked_object vo , dba_objects do 
where vo.object_id = do.object_id;

방법4 : lock 발생 사용자 및 object조회  +어떤 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
   ;

---

lock 걸린 세션 해제 하는 방법

alter system kill session 'sid, serial';
alter system kill session '258, 23542';

💡 DBA가 kill session 명령을 주었을때 어떤 세션은 금방 Kill 되지만, 어떤 세션은 하던 작업때문에 바로 Kill 할 수 없고 하던 작업을 정리하는 시간이 필요할 수 있습니다.

주로 조회SQL을 실행하던 세션의 경우는 바로 Kill 이 가능하지만, 대량의 Update 나 Delete 작업 같은 DML 작업을 하고 있는 세션이었다면 Kill 명령을 받는 순간 롤백 작업에 들어가야 하고, 이 롤백 작업시간은 지금까지 했던 DML 시간 만큼은 걸리기 때문에 바로 Kill이 안됩니다.

(저는 kill이 안되는줄 알고,,, 계속 이 작업을 반복했다는,,,,, 시간 지나니깐 kill되었더라구요,,,(20시간은 걸린듯 해요..) 그럼에도 kill이 안되면 아래 코드를 실행해보세요!!)

kill -9 sid번호