Andrew Fraser DBA

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

Attempting to shrink files which have a lot of empty space can fail with this error:

SQL> alter database datafile '/ora1data/TEST/TEST_ts_data1_f1.dbf' resize 2g ;

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

Provided you can get an outage, one fix for this is to temporarily move segments to another tablespace using a scripts like this. (Note that this script includes the commands to move LOBs).

set pages 9999 lines 132
spool go.tmp
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 ;'
from dba_tables
where tablespace_name = 'TS_DATA1'
order by 1
/
select 'alter index '||owner||'.'||index_name||' rebuild tablespace ts_data2 ;'
from dba_indexes
where tablespace_name = 'TS_DATA1'
and index_type != 'LOB'
order by 1
/
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 lob ('||column_name||') store as (tablespace ts_data2) ;'
from dba_lobs
where tablespace_name = 'TS_DATA1'
order by 1
/
spool off
ed go.tmp

They can be moved back afterwards, once the original tablespace has been shrunk, if desired.

One thing to watch out for is user tablespace quotas during the moves. If you get errors like this:

ORA-01950: no privileges on tablespace 'TS_DATA2'

You either need to temporarily grant unlimited tablespace privilege to the user affected, or grant them a quota on the new tablespace.