How do we free up disk space from allocated namespace? / ORA-03297
You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.

First try and "shrink space" for each affected table:

alter table fragmentedtable enable row movement;

alter table fragmentedtable shrink space;


Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.

The following (taken from Ask Tom &amp; assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:

select file_name,
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
       ceil( blocks*8192/1024/1024) CURRENT_SIZE,
       ceil( blocks*8192/1024/1024) -
       ceil( (nvl(hwm,1)*8192)/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(+)


For example:

FILE_NAME                                           SHRINK_TO CURRENT_SIZE    SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf              696          700          4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf              579          610         31
/u01/app/oracle/oradata/PHIL112/users01.dbf                85           93          8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf              65          120         55

SQL&gt;


To resize, use:

alter database datafile '/path/to/data/file.dbf' resize 100m; 


The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.