Datafile Resize

select df.tablespace_name “Tablespace”,

round(df.TBS_byte /1048576,2) “Total(MB)”,

round((df.TBS_byte – fs.Free_byte)/1048576,2) “Used(MB)”,

round(fs.Free_byte /1048576,2) “Free(MB)”,

round((fs.Free_byte/df.TBS_byte) *100,0) “Free(%)”,

fs.pieces “Pieces”,

round(fs.Max_free /1048576,2) “MaxFree(MB)”,

db.EXTENT_MANAGEMENT

from ( select tablespace_name, sum(bytes) TBS_byte

from dba_data_files group by tablespace_name ) df,

( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces

from dba_free_space group by tablespace_name ) fs,

( select tablespace_name, initial_extent, next_extent,EXTENT_MANAGEMENT

from dba_tablespaces ) db

where df.tablespace_name = db.tablespace_name

and df.tablespace_name = fs.tablespace_name(+)

order by 5

/

set lines 300

set pages 1000

col file_name for a50

col tablespace_name for a20

select FILE_ID,

FILE_NAME,

TABLESPACE_NAME,

AUTOEXTENSIBLE,

INCREMENT_BY,

BYTES/1024/1024 “current(M)”,

MAXBYTES/1024/1024 “maxExtend(M)”

from dba_data_files

order by 3,4 desc

/

select block_id, blocks from dba_extents where file_id=’6′ order by block_id;

select block_id, blocks from dba_extents where file_id=’7′ order by block_id;

select block_id, blocks from dba_extents where file_id=’8′ order by block_id;

select block_id, blocks from dba_extents where file_id=’9′ order by block_id;

show parameter block_size

4번에서 조회 된 사항으로 계산을 합니다

(마지막 block_id * block_size) + (마지막 block * block_size)

test01.dbf

((896 * 8192) + (128 * 8192))/1024/1024 = 8 M

test02.dbf

((640 * 8192) + (128 * 8192))/1024/1024 = 6 M

test03.dbf

((768 * 8192) + (128 * 8192))/1024/1024 = 7 M

test04.dbf

((640 * 8192) + (128 * 8192))/1024/1024 = 6 M

alter database datafile ‘/u01/app/oracle/oradata/SOON1004/test01.dbf’ resize 8M;

alter database datafile ‘/u01/app/oracle/oradata/SOON1004/test02.dbf’ resize 6M;

alter database datafile ‘/u01/app/oracle/oradata/SOON1004/test03.dbf’ resize 7M;

alter database datafile ‘/u01/app/oracle/oradata/SOON1004/test04.dbf’ resize 6M;

해당 사용률 보다 낮게 리사이즈 시 에러가 발생합니다.

블럭 단위로 사용률을 조회 해 resize 하는 방법이지만 실제 사용하는 사이즈 보다 줄지 않음.