Shrink Datafiles – Adjusting HWM
This below example is derived from “Shrinking datafiles” scripts from asktom.oracle.com
Scenario:-
Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb.
1) Created a table
2) Inserted 50millions of rows with commit
— Data file size extended from 100mb to 20gb.
3) performed so many DML’s
— Data file size extended from 100mb to >20gb.
4) Truncated Table/Dropped Table.
Space will can be used further to use for other objects in datafile. But as per my requirement when i really won’t use that datafile, then what is the use to keep datafile size > 20gb and which is causing much disk usage at OS level? Instead of that check the HWM of datafile, at what minimum size you can resize it?
Script for MAX-Shrink:-
set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size'; / select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc /
In lower versions you can use below query to find out possible savings from each data files of Database.
set linesize 400 col tablespace_name format a15 col file_size format 99999 col file_name format a50 col hwm format 99999 col can_save format 99999 SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name, ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de, (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs WHERE ddf.file_id = ebf.file_id AND de.file_id = ebf.file_id AND de.block_id = ebf.maximum ORDER BY 1,2);
Sample Output of above query:-
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf 13,697 32,708 19,011
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf 13,761 32,748 18,987
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf 13,953 32,748 18,795
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf 13,953 32,728 18,775
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf 15,681 32,738 17,057
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf 15,681 32,718 17,037
/u02/oradata/cpdevdb/psindex_01.dbf 11,285 26,718 15,433
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf 22,913 32,728 9,815
/u02/oradata/cpdevdb/cpled_2010_1.dbf 7,721 10,858 3,137
/u02/oradata/cpdevdb/cpled_2009_1.dbf 4,745 6,828 2,083
/u02/oradata/cpdevdb/cpled_2008_1.dbf 4,873 6,948 2,075
/u02/oradata/cpdevdb/cpled_2009_2.dbf 4,825 6,728 1,903
/u02/oradata/cpdevdb/cpled_idx_1.dbf 19,905 21,000 1,095
--------
sum 145,203
By the output i can reclaim space around 141gb of space, without reorganizing any objects. 🙂
Resize Datafiles:-
We can resize datafile up to “Smallest Size Poss” value (or) we can assign any fixed size (or) On top of that we can enable autoextend up to maximum size of datafile.
alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' resize 13700m; alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' autoextend on next 10m maxsize 32767m;
After resizing datafile, Possible savings recorded is around 19GB.
Execute above Script to check changes after resizing of datafiles:-
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf 13,697 13,700 3
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf 13,761 13,770 9
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf 13,953 13,970 17
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf 13,953 13,970 17
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf 15,681 15,690 9
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf 15,681 15,690 9
/u02/oradata/cpdevdb/psindex_01.dbf 11,285 11,300 15
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf 22,913 22,920 7
/u02/oradata/cpdevdb/cpled_2010_1.dbf 7,721 7,730 9
/u02/oradata/cpdevdb/cpled_2009_1.dbf 4,745 4,750 5
/u02/oradata/cpdevdb/cpled_2008_1.dbf 4,873 4,900 27
/u02/oradata/cpdevdb/cpled_2009_2.dbf 4,825 4,850 25
/u02/oradata/cpdevdb/cpled_idx_1.dbf 19,905 19,910 5
--------
sum 157
In earlier output possible savings is 141gb, Now possible savings is 157mb, So we reclaimed ~141gb of space at OS level. 🙂
Now lets compare OS level free space Before & After
Before:-
$df -h /u02
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vgemc4-lvu02
1.5T 1.3T 161G 89% /u02
After:-
$df -h /u02
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vgemc4-lvu02
1.5T 942G 461G 68% /u02
I able to reclaim space by adjusting HWM from two databases around 300GB. Here you go……………
Note:- I have reclaimed for other databases reside on that server, so you can see much differences before and after.
