TIP #21: Shrink datafiles

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.
This URL has some useful query for shrinking :
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--- to shrink datafiles:

select bytes/1024/1024 real_size,ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) shrinked_size,
bytes/1024/1024-ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) released_size
,'alter database datafile '|| ''''||file_name||'''' || ' resize ' || ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) || ' m;' cmd
from
dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
where
tablespace_name='&ts_name'
and
a.file_id = b.file_id(+)
and ceil(blocks*&DB_BLOCK_SIZE/1024/1024)- ceil((nvl(hwm,1)* &DB_BLOCK_SIZE)/1024/1024 ) > 0;

--- To find which objects have extents at the end of datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;