All of us have faced ORA-03297 when shrinking a datafile post a cleanup activity. Shrinking a datafile sounds pretty straight-forward but it depends if the purging did reduced the high watermark on the datafile or the high watermark is still at the end of the datafile and you have chunks of free space below the watermark.
Oracle cannot shrink the datafile below the high watermark and if you attempt to shrink the file below that high watermark you’ll get ORA-03297.
Error report - ORA-03297: file contains used data beyond requested RESIZE value 03297. 00000 - "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.
Now the fix for ORA-03297 as per the documentation is to “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”. But the question is how do I see where the high watermark is and what segments are containing extents near that high watermark so that we can move them to bring down the high watermark. For this we have following queries:
Query 1: This Query will show the high watermark for all the datafiles:
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) Resizable 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 );
This query is very helpful as it not only tells the size where we have the high watermark for that datafile but also explicitly tells you how much space is available for resize operation (RESIZABLE Column of the output):
TABLESPACE_NAME FILE_ID DATA_FILE_NAME ALLOCATED_MBYTES HIGH_WATER_MARK_MBYTES FREE_MBYTES % Free RESIZABLE __________________ __________ _________________________________________________________________ ___________________ _________________________ ______________ _________ ____________ NCOMMON 13 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_ncommon_jj2k6oto_.dbf 5158.6875 4201 965.1875 18.7 957.68 PS_VAL_202107 6 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_ps_val_2_jj2g6qvp_.dbf 1667685 1667684.31 1045672.25 62.7 0.69 RDSADMIN 5 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_rdsadmin_j91jnpyj_.dbf 8 6.56 1.4375 17.96 1.44 SYSAUX 2 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_sysaux_j91hqhr5_.dbf 581.625 553.5 28.125 4.83 28.12 SYSTEM 1 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_system_j91hqf7y_.dbf 700 606 94.1875 13.45 94 UCOMMON 17 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_ucommon_jj2k6q80_.dbf 2058 2056 325.9375 15.83 2 UNDO_T1 3 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_undo_t1_j91hqjl1_.dbf 43020 43154 6 0.01 -134 USERS 4 /rdsdbdata/db/ORN12S11_A/datafile/o1_mf_users_j91hqjtg_.dbf 808.25 389 419.875 51.94 419.25
Now with the above query we know where is the high watermark but what if its at the end of the datafile and we have 0 space available in RESIZABLE although we have sufficient free space in the tablespace.
For this we can always export and then re-import the schema using datapump and that will bring down the high watermark and you can then easily resize and shrink the datafile but most of the time its not practically possible due to additional space requirements or additional downtime and many other reasons, in such cases we have to identify what segments are having their extents near the high watermark of the datafile and then we explicitly rebuild those segments rather than the whole schema. And following query can be used to identify these segments.
Query 2: This query would list the segments that have extents near the high watermark, it expects file_id as input which could be extracted from Query #1.
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 and ddf.file_id = de.file_id and ddf.tablespace_name = dt.tablespace_name order by de.block_id desc ) where rownum <= 100;
You can modify the predicate (where rownum <= 100) to whatever value that suits your requirement.
Then depending on the SEGMENT_TYPE (INDEX/TABLE etc) you can take necessary actions to rebuild the segment, and when an object is rebuild the extents are allocated from the nearest chunk of free space from the used space and that’s how these extends are removed from the high watermark area which lowers down the high watermark.
For SEGMENT_TYPE = INDEX, you can simple rebuild the INDEX Online:
ALTER INDEX &INDEX_NAME REBUILD ONLINE;
For SEGMENT_TYPE = TABLE, you can move the table to another tablespace and then move it back:
ALTER TABLE &TABLE_NAME MOVE TABLESPACE &TBS;
For other SEGMENT_TYPE you can simply script them out using DBMS_METADATA Package and then drop and re-create.
Using this you can bring down the high watermark which would increase the RESIZABLE size in the output of Query #1 and then you can simply resize your datafile:
ALTER DATABASE DATAFILE '&DATAFILE_NAME' RESIZE &SIZE;
Hope you find this tip useful, comment below if you face a SEGMENT_TYPE which you cannot move or any other scenario where you can’t lower the high watermark.