In developpement, it can happen that you may want to shrink oracle datafiles after a huge delete (ie. drop user, drop big table, ...).

Here is a simple way to shrink datafiles using RESIZE option, but it may not be the most efficient.

RESIZE datafiles to the minimal

The following script will generate a sql file to resize every datafiles to the minimal size possible with RESIZE option.

--
-- ResizeAllDatafiles.sql
-- Run this script as sysdba
--

WHENEVER OSERROR EXIT FAILURE ;
WHENEVER SQLERROR EXIT FAILURE ;

SET TERM OFF VERIFY OFF FEED OFF ECHO OFF;
SET PAGES 0 LINES 255;

COLUMN SqlCmd FORMAT A120 WORD_WRAPPED ;
COLUMN DB_BLOCK_SIZE NEW_VALUE DbBlkSize_var ;

-- Retrieves db block size into DbBlkSize_var
SELECT value DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME) = 'DB_BLOCK_SIZE' ;

SET TERM ON ;

PROMPT -- ;
PROMPT -- DB_BLOCK_SIZE: &DbBlkSize_var. ;
PROMPT -- SQL QUERY IN PROGRESS, IT CAN TAKE A WHILE ... ;
PROMPT -- ;

SPOOL ResizeAllDatafiles.gen.sql ;

SELECT 'SET ECHO ON ;' SqlCmd FROM dual ;

SELECT 'ALTER DATABASE DATAFILE '''
    || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1) * &&DbBlkSize_var.) /1024 /1024 ) || 'M;'
    || ' -- CurrentSize=' || CEIL( BLOCKS * &&DbBlkSize_var. /1024 /1024) || 'M'
    SqlCmd
FROM DBA_DATA_FILES DBADF
LEFT JOIN (SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
    ON DBADF.FILE_ID = DBAFS.FILE_ID
WHERE CEIL(BLOCKS * &&DbBlkSize_var. /1024 /1024) - CEIL((NVL(HWM,1) * &&DbBlkSize_var.) /1024 /1024 ) > 0 ;

SPOOL OFF ;

PROMPT -- ResizeAllDatafiles.gen.sql has been generated ;
PROMPT -- Run it as sysdba to resize all your datafiles ;

EXIT;

Go further with RESIZE

Actually RESIZE option can only shrink the datafile over the lastest block in use.
So it may happen that you won't be able to shrink a datafile even if less than 1% is used.

We can try to move objects at the beginning of the datafile in order to get a more efficient RESIZE.

TODO
complete this part

More efficient way for temporary datafile

The RESIZE option can be used for temporary datafile but it may not be very efficient with this kind of file.

If so, read this article: shrink-temporary-datafile.html

Related articles
- oraclespin.wordpress.com

Enjoyed this article? Please like it or share it.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh

Comments   

Wilhemina
# Shrink datafiles with RESIZEWilhemina 2016-04-01 21:56
A fascinating discussion is definitely worth comment. I do believe that you need to publish more on this subject, it might not be a taboo subject but typically
people don't talk about these subjects. To the next! Many
thanks!!
Reply | Reply with quote | Quote