oracle dba_data_files의 maxsize

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

DB 환경 : Oracle Database 18.0.0.0

방법 : oracle dba_data_files의 maxsize 테스트

모든 테이블 스페이스 사이즈를 100mb로 할당하고 autoextends를 on으로 하고 maxsize를 주는경우, autoextends를 on으로 하고 maxsize를 안주는경우, autoextends를 off로 하는 경우를 테스트(maxsize 는 200m 지정)

1. autoextends를 on으로 하고 maxsize를 주고 테이블 스페이스 생성(test01)

SQL> create tablespace test01 datafile '/oracle/data01.dbf' size 100m autoextend on next 10m maxsize 200m;

2. autoextends를 on으로 하고 maxsize를 안주고 테이블 스페이스 생성(test02)

SQL> create tablespace test02 datafile '/oracle/data02.dbf' size 100m autoextend on;

3. autoextends를 off으로 하고 테이블 스페이스 생성(test03)

SQL> create tablespace test03 datafile '/oracle/data03.dbf' size 100m autoextend off;

4. 각 테이블 스페이스 정보 확인

SQL>
set lines 200
set pages 1000
col tablespace_name for a20
col file_name for a30
select tablespace_name, file_name, autoextensible, bytes/1048576 mb, maxbytes/1048576 max_mb, maxblocks
from dba_data_files 
where tablespace_name like 'TEST%'
order by 1,2;

결과

1. autoextends를 on으로 하고 maxsize를 주고 테이블 스페이스 생성(test01)

=> maxsize가 지정해준 200MB로 할당됨

2. autoextends를 on으로 하고 maxsize를 안주고 테이블 스페이스 생성(test02)

=> maxsize가 32767.9844 로 할당됨

이는 데이터파일의 최대 용량

(최대 datafile size = db_block_size * maximum number of blocks)

 최대 datafile size = 8192(현재 db block size(8k)) * 4194302 (위 스크린샷의 maxblocks)

= 34359721984 bytes=> GB로 환산 => 33554416/1024/1024/1024 => 31.99GB 

최대 32GB 인것을 확인가능함

3. autoextends를 off으로 하고 테이블 스페이스 생성(test03)

=> maxsize가 0, autoextends가 off이기때문에 자동으로 늘어나지 않아서 maxsize 0임

추가 테스트1

1. maxsize 를 할당된 값보다 작게 설정

SQL> alter database datafile '/oracle/data01.dbf' autoextend on maxsize 50m;

2. db버전별 각 테이블 스페이스 정보 확인

SQL>
set lines 200
set pages 1000
col tablespace_name for a20
col file_name for a30
select tablespace_name, file_name, autoextensible, bytes/1048576 mb, maxbytes/1048576 max_mb, maxblocks
from dba_data_files 
where tablespace_name like 'TEST%'
order by 1,2;

결과 

autoextends를 on으로 하고 maxsize(maxbytes)를 bytes 보다 작게 설정

9i,10g => maxsize가 지정해준 50MB로 설정됨(maxbytes가 bytes 보다 적은 현상 발생)

11g 이상 => maxsize가 지정해준 50MB로 할당되지 않고 bytes size 인 100MB로 설정됨

추가 테스트2

0. 원복 

SQL> alter database datafile '/oracle/data01.dbf' autoextend on maxsize 200m;

or

SQL> drop tablespace test01 including contents and datafiles;
SQL> create tablespace test01 datafile '/oracle/data01.dbf' size 100m autoextend on next 10m maxsize 200m;

1. size(bytes)를 maxsize 값 보다 크게 설정

SQL> alter database datafile '/oracle/data01.dbf' resize 300m;

2. db버전별 각 테이블 스페이스 정보 확인

SQL>
set lines 200
set pages 1000
col tablespace_name for a20
col file_name for a30
select tablespace_name, file_name, autoextensible, bytes/1048576 mb, maxbytes/1048576 max_mb, maxblocks
from dba_data_files 
where tablespace_name like 'TEST%'
order by 1,2;

결과

autoextends를 on으로 하고 bytes를 maxsize(maxbytes) 보다 크게 설정

9i,10g,11g 그이상 동일 => bytes가 지정해준 300MB로 설정됨(maxbytes가 bytes 보다 적은 현상 발생)

*maxsize 는 autoextend 자동확장 될때 최대치를 잡는 것이기 때문에

alter database datafile 00 resize 300m; 등 maxsize 보다 높은 값을 지정하면 autoextend는 무용지물이 된다.

참조 :