Shrink your Tablespace

Hello fellow DBAs. As you all are probably aware, sometimes we need to shrink Tablespaces and we don’t have a fancy graphical doodad at hand all the time, so we need to get this done with our trusted command line SQL*Plus and doing so without using trial and error to find the maximum possible size, because there ususally is data somewhere in theses files.

If you try to make it too small, you’ll get the following fancy error:

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

To get around this, I found a script written by Franck Pachot who worked for DBI Services back in the day. (Click here for his original script)

Though I liked this script, I had two, let’s call it personal issues with it. First, where was the problem, that sometimes I got the ORA-03297 despite using the script output on databases which had a current workload and continously changes going on. Second, if autoextend is turned off for a datafile it would only give you a comment, not the resize command. Third, sometimes if you were able to reclaim really really much from a file it would give you only „#####“ instead of the actual number (this is only a beauty issue). So, here is Francks script with the few amendments for my daily work.

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
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes-102400)/1024/1024),999999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||(ceil(hwm_bytes/1024/1024)+100)||'M;'
from hwmdf
where
 bytes-hwm_bytes-102400>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/