오라클 11gR2 ddl wait (ddl_lock_timeout 파라미터)

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : 오라클 11gR2 ddl wait (ddl_lock_timeout 파라미터)

오라클에서 테이블에 ddl(partition exchange, add column 등) 작업을 할때 

해당 테이블에 트랜잭션이 존재하는 경우(dml 등)

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 메세지가 발생할 수 있음

이때 ddl_lock_timeout 파라미터를 이용해 ddl wait 기능을 사용하면 지정한 대기시간 만큼 기다리면서

연속적으로 ddl을 시도 하므로 ddl의 성공확률을 높이고 관리자의 불편함이 해소될 수 있음

 

 

아래 시나리오를 통해 테스트해봄

테스트1. 세션1에서 insert 후 세션2에서 ddl 실행

테스트2. 세션1에서 update 후 세션2에서 ddl 실행

테스트3. 세션1에서 delete 후 세션2에서 ddl 실행

 

 

테스트1. 세션1에서 insert 후 세션2에서 ddl 실행

session1
SQL> 
create table imsitab (c1 number);
insert into imsitab values (1);
(commit 하지 않고 대기)
 
session2
SQL> 
alter table imsitab add (c2 number);
(lock 발생하여 대기함)

 

 

세션1 commit 이후 정상적으로 alert 됨

session1
SQL> commit;
 
session2
Table altered.
(정상적으로 alert 됨)

 

 

테스트2. 세션1에서 update 후 세션2에서 ddl 실행

session1
SQL> 
update imsitab c1=2
where c1=1;
(commit 하지 않고 대기)
 
session2
SQL> alter table imsitab add (c3 number);
(lock 발생하여 대기함)

 

 

세션1 commit 이후 정상적으로 alert 됨

session1
SQL> commit;
 
session2
Table altered.
(정상적으로 alert 됨)

 

 

테스트3. 세션1에서 delete 후 세션2에서 ddl 실행

session1
SQL> 
delete imsitab 
where c1=2;
(commit 하지 않고 대기)
 
session2
SQL> alter table imsitab add (c4 number);
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
(에러 발생)

 

 

ddl_lock_timeout 파라미터 설정

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

 

 

100초 이전에 session1에서 commit;

session1
SQL> commit;
 
session2
Table altered.
(정상적으로 alert 됨)

 

 

참조 : 

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