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

Today we are talking about the steps we use to solve the “error ORA-03297: file contains used data beyond requested RESIZE value”

Let’s have look on the steps.

QUERY 1 – datafile Allocated space, free space, High Water Mark location,%Free and resizeable MB

select tablespace_name,
file_id,
file_name DATA_FILE_NAME,
Allocated_MBYTES,
High_Water_Mark_MBYTES,
FREE_MBYTES,
trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",
trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizeble
from
(
select ddf.tablespace_name tablespace_name,
ddf.file_id file_id,
ddf.file_name file_name,
ddf.bytes/1024/1024 Allocated_MBYTES,
trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,
FREE_MBYTES
from
dba_data_files ddf,
dba_tablespaces dt,
(
select file_id, sum(bytes/1024/1024) FREE_MBYTES
from dba_free_space
group by file_id
) free,
(
select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id
) ex
where ddf.file_id = ex.file_id
and ddf.tablespace_name = dt.tablespace_name
and ddf.file_id = free.file_id (+)
order by ddf.tablespace_name, ddf.file_id
);

QUERY 2 – location of segments within a data file (Provide file_id of the Tablespace)

select file_name data_file_name,
segment_type,
owner||'.'||segment_name segment_name,
partition_name,
block_id,
blockId_Mbytes
from
(
select
de.owner owner,
de.segment_name segment_name,
de.segment_type segment_type,
de.block_id block_id,
DE.PARTITION_NAME partition_name,
ddf.file_name file_name,
trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes
from
dba_extents de, dba_data_files ddf, dba_tablespaces dt
where ddf.file_id = &file_id
nd ddf.file_id = de.file_id
and ddf.tablespace_name = dt.tablespace_name
order by de.block_id desc
)
where rownum <= 100;

To re-size the DATAFILE the only way is to lower the High Water Mark.  It can be done by moving extents to other Tablespace or the same Tablespace. Check the size of Tablespace before moving.  In the production database, it’s not easy to move the table, LOB etc since many users can be accessing these objects. An index can be the move by rebuilding online in a production database. After moving the tables it’s necessary to rebuild the UNUSABLE indexes on the tables.

Today’s thought 

Work like you don’t need the money. Love like you’ve never been hurt. Dance like nobody’s watching — By Satchel Paige

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp