Monday, July 16, 2012

Shrink data files in Oracle

Oracle is smart and stupid at the same time. Maybe it only smart and it is I that is stupid, but that is not the point.
Oracle has a wonderful function that can auto extend your data files when it need to, when data is growing in your database. Oracle is stupid don't have a built is function to shrink those files once the data is removed and the space is no longer needed. Some may argue that it is smart that oracle has that space allocated for future use. That might be true but I want more function.

I found a script i frequanlty use too shrink my data files on test and development environments:
This will produce a list of command to shrink data files as much as it can without defragging or reorganization the data. It looks for the data block with the highest block_id that contains data and calculates how much space after that block can be removed.

select
  'ALTER DATABASE DATAFILE ''' ||
  file_name ||
  ''' RESIZE ' ||
  ceil ( (nvl (hwm, 1) * 8192) / 1024 / 1024)  ||
  'M;' shrink_datafiles
from
  dba_data_files dbadf,
  (    
    select
      file_id, max (block_id + blocks - 1) hwm
    from
      dba_extents
    group by
      file_id
  )
  dbafs
where  dbadf.file_id = dbafs.file_id(+)
and ceil (blocks * 8192 / 1024 / 1024) - ceil ( (nvl (hwm, 1) * 8192) / 1024 / 1024) > 0;
/



The list look something like this:

SHRINK_DATAFILES
------------------------------------------------------------
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\SYSTEM01.DBF' RESIZE 742M; 
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\SYSAUX01.DBF' RESIZE 898M; 
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\USERS01.DBF' RESIZE 39M; 
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\UNDOTBS01.DBF' RESIZE 10401M; 
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\APXML1PR\APEX01.DBF' RESIZE 198M; 

2 comments:

  1. I should change the script as follows:

    select 'ALTER DATABASE DATAFILE '''||file_name||''' RESIZE '||ceil ( (nvl (hwm, 1) * dbats.block_size) / 1024 / 1024)||'M;' shrink_datafiles
    from dba_data_files dbadf,
    ( select file_id, max (block_id + blocks - 1) hwm
    from dba_extents
    group by file_id
    ) dbafs,
    dba_tablespaces dbats
    where dbats.tablespace_name = dbadf.tablespace_name and
    dbadf.file_id = dbafs.file_id(+) and
    ceil (blocks * dbats.block_size / 1024 / 1024) - ceil ( (nvl (hwm, 1) * dbats.block_size) / 1024 / 1024) > 0
    /

    This due to that blocksize maybe is not 8K on all datafiles in a database.

    ReplyDelete