How to Shrink an Oracle Tablespace

My disk was full because the USERS Tablespace was bigger than 17Gb. Here are the steps to shrink an Oracle Tablespace:

1. PURGE TABLESPACE;

Example:

purge tablespace USERS;

2. ALTER TABLESPACE <TableSpace> COALESCE;

Example:

alter tablespace USERS coalesce;

3. Change the TableSpace Size:

alter database datafile '<FullTableSpacePathWithName>'resize <NewSize>M;

Example:

alter database datafile 'C:ORACLEPRODUCT10.1.0ORADATAORA10GUSERS01.DBF'resize 10000M;

Errors you might get:

ORA-03297: file contains used data beyond requested RESIZE value

It means that the new size is too small. You must increase the number of the size you want.

You can use the SQL statement bellow to see the max values for the Tablespaces:

select a.tablespace_name, a.file_name,(b.maximum+c.blocks-1)*d.db_block_size highwater

from dba_data_files a,(select file_id,max(block_id)
     maximum from dba_extents group by file_id) b ,dba_extents c ,
     (select value db_block_size from v$parameter
     where name='db_block_size') d
where a.file_id = b.file_id
      and c.file_id = b.file_id
      and c.block_id = b.maximum
order by a.tablespace_name,a.file_name