Shrink data files in Oracle

Oracle has a wonderful function that can auto extend your data files when it need to, when data is growing in your database. But it don't have a built is function to shrink those files once the data is removed and the space is no longer needed. Some may argue that it is smart that oracle has that space allocated for future use. That might be true but I want more function.

I found a script i frequanlty use too shrink my data files on test and development environments:

This will produce a list of command to shrink data files as much as it can without defragging or reorganization the data. It looks for the data block with the highest block_id that contains data and calculates how much space after that block can be removed.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select   'ALTER DATABASE DATAFILE ''' ||   file_name ||   ''' RESIZE ' ||   ceil ( (nvl (hwm, 1) * 8192) / 1024 / 1024)  ||   'M;' shrink_datafiles from   dba_data_files dbadf,   (        select       file_id, max (block_id + blocks - 1) hwm     from       dba_extents     group by       file_id   )   dbafs where  dbadf.file_id = dbafs.file_id(+) and ceil (blocks * 8192 / 1024 / 1024) - ceil ( (nvl (hwm, 1) * 8192) / 1024 / 1024) > 0; /

The list look something like this:

?

1 2 3 4 5 6 7

SHRINK_DATAFILES ------------------------------------------------------------ ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\SYSTEM01.DBF' RESIZE 742M; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\SYSAUX01.DBF' RESIZE 898M; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\USERS01.DBF' RESIZE 39M; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\UNDOTBS01.DBF' RESIZE 10401M; ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\APEX01.DBF' RESIZE 198M;

I got this script from http://www.articles.freemegazone.com/shrinking-oracle-datafiles.php