Need Space? Shrink Datafiles!

The SQL query below comes in handy if you need to downsize the database. It shows you the space that can be freed per file and generates the alter database command.

set verify off linesize 120 pagesize 100
column file_name format a50 word_wrapped heading "File Name"
column usedmb   format 999,990 heading "Used|Size MB"
column currsize format 999,990 heading "Current|Size MB"
column savings  format 999,990 heading "Poss.|Savings MB"
break on report
compute sum of savings on report;

select file_name
,      ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) usedmb
,      ceil( blocks*tsp.block_size/1024/1024) currsize
,      ceil( blocks*tsp.block_size/1024/1024) -
       ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) savings
from   dba_data_files df
  left outer join (
         select file_id
         ,      max( block_id + blocks-1 ) hwm
         from   (   select file_id
                    ,      block_id
                    ,      blocks
                    from   dba_extents
                )
         group by file_id
                  ) ext
               on df.file_id = ext.file_id
  inner join dba_tablespaces tsp
               on df.tablespace_name = tsp.tablespace_name
order by savings desc
;

select file_name
,      ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) usedmb
,      ceil( blocks*tsp.block_size/1024/1024) currsize
,      ceil( blocks*tsp.block_size/1024/1024) -
       ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) savings
from dba_data_files df
  left outer join (
         select file_id
         ,      max( block_id + blocks-1 ) hwm
         from   (   -- from dba_extents, works faster but doesn't take all extents
                    select f.file# file_id
                    ,      e.ktfbuebno block_id
                    ,      e.ktfbueblks blocks
                    from   sys.x$ktfbue e
                      inner join sys.file$ f
                              on (   e.ktfbuesegtsn = f.ts#
                                 and e.ktfbuefno    = f.relfile#
                                 )
                )
         group by file_id
                  ) ext
               on df.file_id = ext.file_id
  inner join dba_tablespaces tsp
               on df.tablespace_name = tsp.tablespace_name
order by savings desc
;

column cmd format a120 heading "Resize Command";

select 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 )  || 'm;' cmd
from   dba_data_files df
  left outer join (
         select file_id
         ,      max( block_id + blocks-1 ) hwm
         from   (   -- from dba_extents, works faster but doesn't take all extents
                    select f.file# file_id
                    ,      e.ktfbuebno block_id
                    ,      e.ktfbueblks blocks
                    from   sys.x$ktfbue e
                      inner join sys.file$ f
                              on (   e.ktfbuesegtsn = f.ts#
                                 and e.ktfbuefno    = f.relfile#
                                 )
                )
         group by file_id
                  ) ext
               on df.file_id = ext.file_id
  inner join dba_tablespaces tsp
               on df.tablespace_name = tsp.tablespace_name
where ceil( blocks*tsp.block_size/1024/1024) - ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) > 0
order by ceil( blocks*tsp.block_size/1024/1024) - ceil( (nvl(hwm,1)*tsp.block_size)/1024/1024 ) desc
;

Notice that I have listed two slightly different versions of the query. The first one makes use of dba_extents. Reading from this system view does not perform if you have many objects and extents in your database. Therefore, I am using direct access to the fixed tables x$ktfbue and file$ in the second version. This makes it much faster, but requires sysdba access.