Resize operation completed for file# n 이란

OS환경 : Oracle Linux7.3(64bit)

DB 환경 : Oracle Database 12.2.0.1

에러 : Resize operation completed for file# 4, old size 624640K, new size 634880K

2018-11-08T11:22:32.299393+09:00
Resize operation completed for file# 4, old size 624640K, new size 634880K
2018-11-08T11:22:38.806832+09:00
Resize operation completed for file# 4, old size 634880K, new size 645120K
2018-11-08T11:22:41.948760+09:00
Resize operation completed for file# 4, old size 645120K, new size 655360K
2018-11-08T11:22:46.213798+09:00
Resize operation completed for file# 4, old size 655360K, new size 665600K
2018-11-08T11:22:49.946632+09:00
Resize operation completed for file# 4, old size 665600K, new size 675840K

해결 방법 : 

사용량이 많은 시스템에서는 alert log에 resize 메시지가 가득 찰 수 있음

아래 파라미터 수정으로 로그 발생을 막을수 있음

SQL> alter system set "_disable_file_resize_logging"=TRUE ; (default 값은 FALSE)

원인 : 

과도한 DML (insert / update / delete)이 있는 사용량이 많은 시스템에서는 alert log에 많은 resize 메시지가 발생할 수 있음

내가 테스트 환경에서 5백만개의 데이터(800MB)이 든 테이블 test을 

insert into test as select * from test;

구문을 실행하여 발생한 메세지

나의경우 alert log 에 나온 file_id를 보니 undo tablespace 였음.

SQL> select * from dba_data_files where file_id =4;
 
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME               BYTES     BLOCKS STATUS      RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ LOST_WR
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- ------- -------
/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
     4 UNDOTBS1               718274560      87680 AVAILABLE         4 YES 3.4360E+10     4194302      640  717225984       87552 ONLINE  OFF
 
1 row selected.

참조 : Resize Operation Completed For File# (문서 ID 1982901.1)