Resize Oracle datafiles down to the minimum without facing ORA-03297

Hi, Today we are going to discuss how to Resize Oracle datafiles down to the minimum without facing ORA-03297.

As we know the data-files stored our data and they are growing daily basis. suppose your data-files have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space (mount points), or you want to move (migrate or on other mount pints) some files without moving empty blocks, or your database backup size is too large. Using ALTER DATABASE DATAFILE … RESIZE can reclaim the free space at the end of the datafile, down to the latest allocated extent.

But if you try to reclaim the space, you will get the following error:
“ORA-03297: file contains used data beyond requested RESIZE value”

So, how do you find this minimum value, which is the data-files high watermark?
You have the solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one.
Or there is the smart solution: find the data-file high watermark.

You can query from DBA_EXTENTS to know that. But did you try on an oracle database with a lot of data-files? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don’t need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.

Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablespaces. And you have to read all of them.

Solution for ORA-03297

Use the following query to get complete details about free space:

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

Output in my case:

/* reclaim      45M from      50M after setting autoextensible maxsize higher than current size for file /u01/dharma01.dbf */
/* reclaim      28M from     550M */ alter database datafile '/u01/app/oracle/oradata/gaming/sysaux01.dbf' resize 523M;
/* reclaim      19M from      50M */ alter database datafile '/u01/app/oracle/oradata/gaming/undotbs01.dbf' resize 32M;
/* reclaim       8M from      10M after setting autoextensible maxsize higher than current size for file /u01/app/oracle/oradata/gaming/und01.dbf */
/* reclaim       8M from     710M */ alter database datafile '/u01/app/oracle/oradata/gaming/system01.dbf' resize 703M;
/* reclaim       4M from       5M */ alter database datafile '/u01/app/oracle/oradata/gaming/users01.dbf' resize 2M;

From the above query, you will get the resize statements directly, with the reclaimable space in comments.

Important points for the above query:

• I generate the resize statements only for data-files that are auto-extensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.
• When data-file is not auto-extensible, or max-size is not higher than the current size, I only generate a comment.
• When a data-file has no extents at all I generate a resize to 5MB. I would like to find the minimum possible size (without getting ORA-3214) but my test does not validate yet what is documented in MOS. If anyone has an idea, please share.
• There is probably a way to get that high water mark in a cheaper way. Because the alter statement gives the ORA-03297 much quicker.
• Information is probably available in the datafile headers, without going to segment headers, but I don’t know if it is exposed in a safe way.

Read - AWR vs ADDM vs ASH

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube: https://www.youtube.com/ocptechnology

Reference article from https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/