Reclaiming disk space from Oracle datafiles

You want to reclaim some valuable Oracle estate – disk space, from your datafile(s).

ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile,

But if you tried it and got the error below, then you must have chosen some arbitrary values which tried to reclaim beyond that is possible.

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

There is a minimum value that you can’t go beyond called the ‘datafile high water mark’.

So, how do you find this minimum value – the datafile’s high water mark? Query the view dba_extents
WARNING!!

On a large database system, do not query the dba_extents  view/table.You can query DBA_EXTENTS to know that. But   It runs forever and is resource-intensive.

SO WHAT DO WE DO

We query  SYS.X$KTFBUE instead, which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.

Here is the query:

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
/
This was what I got from my system
 It generates the resize statements with the right values
/* reclaim    2370M from    2505M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/undotbs01.dbf’ resize 136M;
/* reclaim     593M from    3072M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/euna_data_02.dbf’ resize 2480M;
/* reclaim     248M from     256M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/euna_rsa_data_01.dbf’ resize 9M;
/* reclaim     243M from     256M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/euna_rsa_idx_01.dbf’ resize 14M;
/* reclaim     141M from     142M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/users01.dbf’ resize 2M;
/* reclaim      41M from     710M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/sysaux01.dbf’ resize 670M;
/* reclaim       3M from    1390M */ alter database datafile ‘/u02/app/oracle/oradata/CHARLES/system01.dbf’ resize 1388M;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s