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;
I got this script from http://www.articles.freemegazone.com/shrinking-oracle-datafiles.php
I should change the script as follows:
ReplyDeleteselect '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.
This is a lot better. Thanks
DeleteGreat and I have a swell offer you: What Home Renovation Shows Are On Netflix house renovation ideas interior
ReplyDelete