Shrink Datafiles

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