오라클 19c lob 테이블 allocate extent 시 lock 여부 확인 테스트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c lob 테이블 allocate extent 시 lock 여부 확인 테스트

lob 테이블에 수동으로 extent를 추가하는 명령을 사용할때 lock이 발생하는지 여부를 확인하는 테스트

 

 

테스트1

extent 추가 작업 중 insert 테스트

테이블 삭제 및 테스트 테이블스페이스의 데이터파일 작은 크기로 resize

SQL>
drop table testlob purge;
alter database datafile 6 resize 100m;

 

 

lob 테이블 생성

SQL>
create table testlob
(no number, cl clob) tablespace testlob
lob (cl) store as basicfile
(
storage
(initial 64k
 next 1m
)
chunk 16384
tablespace testlob
);

 

 

데이터 삽입

SQL>
insert into testlob values (1,'a');
commit;

 

 

lob 컬럼 extent 수동 추가 2gb

SQL> alter table testlob modify lob (cl) (allocate extent(size 2g));

 

 

동시에 세션2에서 lock 조회

세션2
SQL>
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
   ;
 
SESSION_ID    SERIAL# OBJECT_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
MACHINE                              TERMINAL            PROGRAM                 ADDRESS           PIECE
---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------------- ----------
SQL_TEXT
----------------------------------------------------------------
    62     4311 TESTLOB
ORACLE19                             pts/0                sqlplus@ORACLE19 (TNS V1-V3) 0000000070532D50       0
alter table testlob modify lob (cl) (allocate extent(size 2g))

 

 

동시에 세션3에서 insert 시도

세션3
SQL> insert into testlob values (1,'a');
(allocate extent 가 끝날때 까지 대기함)

table 변경(allocate extent)으로 인한 락이 발생하여 insert구문이 대기함

 

 

테스트2

insert 작업 중 extent 추가 테스트

테이블 삭제 및 테스트 테이블스페이스의 데이터파일 작은 크기로 resize

세션1
SQL>
drop table testlob purge;
alter database datafile 6 resize 100m;

 

 

lob 테이블 생성

SQL>
create table testlob
(no number, cl clob) tablespace testlob
lob (cl) store as basicfile
(
storage
(initial 64k
 next 1m
)
chunk 16384
tablespace testlob
);

 

 

데이터 삽입

SQL> 
insert into testlob values (1,'a');
commit;

 

 

세션2에서 insert 후 commit 하지 않고 대기

세션2
SQL> insert into testlob values (1,'a');

 

 

세션1에서 extent 수동 추가작업 진행

세션1
SQL> alter table testlob modify lob (cl) (allocate extent(size 10m));
alter table testlob modify lob (cl) (allocate extent(size 10m))
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

에러가 발생함

 

 

결론 : 테이블 사용을 하지 않을 때(dml이 없을 때) 작업을 해야하고 사용중일때 allocate extent 작업을 하면 lock 이 발생해 장애로 이어질 수 있다.

11.2.0.1 버전이하에서는 allocate extent 작업 중 강제로 끊을 경우(Ctrl + c) 블록이 깨지는 버그도 있음

 

 

참조 : https://positivemh.tistory.com/834

오라클 19c 일반테이블 및 lob테이블 컬럼 수동 extent 추가

740075.1, 1229669.1