ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

에러 : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

오라클에서 테이블에 ddl(partition exchange, add column 등) 작업을 할때 
해당 테이블에 트랜잭션이 존재하는 경우(dml 등)
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 메세지가 발생할 수 있음

SQL> alter table imsitab drop (c3);
alter table imsitab drop (c3)
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

 

해결 방법 : ddl_lock_timeout 파라미터 사용 또는 dml 세션 kill

1. ddl_lock_timeout 파라미터 사용

이 파라미터 사용 시 파라미터 값 숫자(초)만큼 오라클이 계속해서 ddl을 시도하고 값 숫자(초)안에 앞선 트랜잭션의 lock이 해제될 경우 ddl이 정상 수행됨

값 숫자(초) 안에 앞선 트랜잭션이 안끝날 경우 ora-00054가 발생함

이때는 시간을 더 늘려서 수행해주거나 2번 방법을 사용하면 됨

SQL> alter session set ddl_lock_timeout = 100;
SQL> alter table imsitab drop (c3);
(100초까지 대기 후 ora-00054 발생)

 

2. v$lock_object 에서 lock 세션 찾아서 kill(rac라면 처음부터 gv$ 뷰를 사용하면 찾기 편함)

1번 방법으로 ddl_lock_timeout 를 걸고 ddl 실행

SQL> alter session set ddl_lock_timeout = 100;
SQL> alter table imsitab drop (c3);

 

 

lock테이블 확인

SQL> 
select lo.session_id,lo.oracle_username,lo.os_user_name,
lo.process,do.object_name,do.subobject_name,
decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',
3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',
to_char(lo.locked_mode)) mode_held
from v$locked_object lo, dba_objects do
where lo.object_id = do.object_id
and do.OBJECT_NAME = '테이블명'
order by 6,7;

 

 

위 결과에서 나온 session_id 를 이용해 조회

SQL> 
select inst_id, sid, serial#, username, status, program, sql_id, blocking_session_status, blocking_instance, blocking_session 
from v$session
where sid=7785;

 

 

위 결과에서 나온 blocking_session 을 이용해 blocking 세션정보 확인

SQL> 
select inst_id, sid, serial#, username, sql_id, status, program, action, event 
from gv$session
where sid=12836;

 

 

위 결과에서 나온 sql_id를 이용해 해당 sql의 sql_text 확인

SQL> 
select sql_id, sql_text from gv$sql
where sql_id = 'f9tj22m4bdsf7';

 

 

kill 가능한 세션이라면 kill

SQL> alter system kill session 'sid,serial#' immediate;

 

 

원인 : dml로 인해 생긴 lock으로 ddl 작업이 실패함 

dml 세션을 kill 하거나 ddl_lock_timeout 파라미터를 세션레벨로 설정해 기다린 뒤 테이블을 변경하는 방법이 있음

 

 

참조 : 

https://positivemh.tistory.com/374
http://haisins.epac.to/wordpress/?p=3542
https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams074.htm

https://positivemh.tistory.com/847