How to shrink a datafile when getting ORA-03297 (Lowering High Watermark)

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.