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

I got ” ORA-03297: file contains used data beyond requested RESIZE value”  error in Oracle database.

 

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

 

Details of error are as follows.

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

Cause: Some portion of the file in the region to be trimmed is currently in use by a database object

Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.
SQL> alter database datafile '/oradata/<SID>/users01.dbf' resize 117248K;
alter database datafile '/oradata/<SID>/users01.dbf' resize 117248K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

 

 

 

file contains used data beyond requested RESIZE value

This ORA-03297 error is related with the Some portion of the file in the region to be trimmed is currently in use by a database object.

The error is due to fact that there are allocated extents between the size to resize/shrink to and the end-of-file (might be space management blocks, so not necessary belonging to a segment).

 

Alter database datafile resize

To solve this error, Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.

Two options do exist to find the highwater mark of a datafile:

– checking backwards for free space at end of file (proven to be the fastest)

– check highest allocated extent in datafile (it might be that space management blocks are above last allocated extend)

 

In case there are blocking segments for resizing/shrinking the datafiles the attached SQL-script: show_segment_above_size.sql will show the segments which do have extents above the desired size.

The script will show the ‘blocking’ segments for the resize, no block_id/location is shown due to fact that whole segments needs to be ‘moved/cleaned up/…’ in order to free up the extents thus enabling the desized resize.

 

1) checking backwards for free space at end of file

Due to added functionality (different blocksizes for tablespaces) the script has been split into:

  • Oracle version 9 and higher
  • Oracle version 8 and lower

 

To solve this error, you can purge the recycle bin and try again. 

Or  you can Try “alter tablespace” coalesce or “alter index” rebuild and try again.

 

If these are not solved your problem, then you should move tables and index to another tablespace.

You should read the following post to learn how to move tables and indexes to another tablespace.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 322 views last month,  6 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply