How Do I Shrink Datafiles to Reclaim space?

There is probably a lot of available space that could be re-claimed by your database server operating system, but it is locked up in Oracle datafiles. Fortunately it is possible to shrink Oracle datafiles. This works in 10.2 and possibly earlier versions, but I am not sure - if you are on anything earlier than 10.2, you have other issues to worry about : ) Here is what this Tom Kyte script will give you: The block size your db is using The possible savings by shrinking each datafile and a total possible savings The SQL to shrink each datafile as far as it can go -- This script was written by Tom Kyte and retrieved from asktom.oracle.com set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "SmallestSizePoss." column currsize format 999,990 heading "CurrentSize" 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(+) / column cmd format a75 word_wrapped select 'alter database datafile '''file_name''' resize ' ceil( (nvl(hwm,1)*&&blksize)/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 a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 /