• Arun Kumar

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

Many a times inside Oracle database, you need to shrink the data file size so that you can re-claim the unused space. Over here, I have discussed various scenarios that will help you fix the ORA-03297 error.


Run Possible Saving Report


Use below script to find the possible space saving inside the database. It will list out the data files along with possible savings in MBs

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
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(+) order by savings desc
/

Alter Tablespace Coalesce


Even though SMON performs tablespace coalesce time to time, still our first step is to try it out and see if it works for us

SQL> alter tablespace users coalesce;

Now try to shrink the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
ALTER DATABASE DATAFILE 72 RESIZE 1G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

Purge Tablespace


Objects belonging to a tablespace might reside under Recyclebin which does not allow you to shrink the datafile. We must remove the tablespace specific segments from recycle bin first

SQL> purge tablespace users;

Lets try to shrink the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
ALTER DATABASE DATAFILE 72 RESIZE 1G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

Purge Recyclebin


The PURGE TABLESPACE command only removes recyclebin segments belonging to the currently connected user. There might be other users who have deleted objects from the tablespace that reside in recyclebin. Its a good idea to purge recyclebin as sysdba

SQL purge recyclebin;

Let's resize the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
Database altered.

Other Methods

  • Re-organize objects inside tablespace using export/import

  • Use alter table shrink space command

  • Use alter index rebuild command

  • Re-organize tab

139 views