DBA Tips for Shrinking Oracle Datafiles!!

 Author: Diane Martin

Being Oracle database administrator you must be aware of the fact that if some chunk of space exists at the end of your datafiles then you can manage things in a better way by shrinking Oracle Datafiles. In this article we will discuss a number of options that will help you efficiently shrink Oracle datafiles.

Finding Objects that have Extents at the end of Datafile:

Sometimes resizing datafile fails because of the objects having extents at the end of the datafile and we need to identify those objects. DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database. You just have to turn the filename of the datafile you want to shrink into a FILE_ID and query DBA_EXTENTS to find all extents allocated in it. BLOCK_ID is the starting block number of the extent. If result set is sorted by BLOCK_ID in descending order then the first N rows you get will be the extents at the end of the file. Below script can be used to find the seven outermost extents in the datafile you specified.

SELECT * FROM
(
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BLOCK_ID
FROM DBA_EXTENTS
WHERE FILE_ID = ( SELECT FILE_ID FROM DBA_DATA_FILES WHERE FILE_NAME = :FILE )
ORDER BY BLOCK_ID DESC
)
WHERE ROWNUM <= 7

Finding how much datafiles can be resized:

You can use below script to find out how much you can resize your datafiles.

SET VERIFY OFF
SQL>COLUMN SMALLEST FORMAT 999,990
SQL>COLUMN CURRSIZE FORMAT 999,990
SQL>COLUMN SAVINGS FORMAT 999,990
SQL>COLUMN FILE_NAME FORMAT A50 WORD_WRAPPED
SET PAGES 60

SQL>BREAK ON REPORT
SQL>COMPUTE SUM OF SAVINGS ON REPORT

COLUMN VALUE NEW_VAL BLKSIZE

SQL> SELECT FILE_NAME,
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
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(+) /

FILE_NAME SMALLEST CURRSIZE SAVINGS
-------------------------------------------------- -------- -------- --------
F:\ORACLE\ORADATA\MYDBF\RBS01.DBF 25 200 170
F:\ORACLE\ORADATA\MYDBF\TEMP01.DBF 1 72 71
F:\ORACLE\ORADATA\MYDBF\INDEX02.DBF 5 58 57
F:\ORACLE\ORADATA\MYDBF\SYSTEM07.DBF 75 264 209
F:\ORACLE\ORADATA\MYDBF\MYTOOL08.DBF 1 12 11
F:\ORACLE\ORADATA\MYDBF\MYUSERP01.DBF 7 108 107

Shrinking Oracle Datafiles (Option -I):

One option to shrink datafiles is to use the ALTER DATABASE DATAFILE command by specifying the minimum value you want to resize it to.

ALTER DATABASE DATAFILE '< YOUR FILE NAME WITH COMPLETE PATH > ' RESIZE <VALUE SAY 500k>

If you can not resize datafile and ends up with ORA-03297 error then you should first query the DBA_EXTENT view to see if the datafile can be shrinked.

SELECT * FROM DBA_EXTENT;

Then you will have to purge the recycle bin.

SQL> PURGE RECYCLEBIN;

Finally you will have to shrink of the data file.

SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\TEMP01.DBF' RESIZE 100 K;

Shrinking Oracle Datafiles (Option -II):

You can also use below script to shrink datafiles to the smallest size that is possible.

SQL>COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
SQL>COLUMN VALUE NEW_VAL BLKSIZE
SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'DB_BLOCK_SIZE' /

SQL>SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/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*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0 /

SHRINK_DATAFILES
---------------------------------------------------------------------------
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\RBS01.DBF' RESIZE 25M;
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\TEMP01.DBF' RESIZE 1M;
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\INDEX02.DBF' RESIZE 5M;
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\SYSTEM07.DBF' RESIZE 75M;
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\MYTOOL08.DBF' RESIZE 1M;
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\MYUSERP01.DBF' RESIZE 7M;
6 rows selected

Shrinking Oracle Datafiles (Option -III):

You can also use below script to generate a report that will be useful for resizing datafiles thus making you ensured that you are getting all free space at end of the file. Before running this script you should run ALTER TABLESPACE <YOUR TABLESPACE NAME> COALESCE command. This will combine all contiguous free extents into larger contiguous extents for each datafile in your tablespace.

SET VERIFY OFF
SQL>COLUMN FILE_NAME FORMAT A50 WORD_WRAPPED
SET PAGES 60
SQL> COL FILE_SIZE_MB FORMAT 99999

SQL>ACCEPT TABLE_SPACE PROMPT "PLEASE ENTER YOUR TABLESPACE_NAME OR ALL:"

SQL>SELECT MY_DBADF.FILE_NAME, MY_DBAFS.FILE_ID, MY_DBADF.BLOCKS,

(MY_DBADF.BLOCKS*VALUE)/1024/1024 FILE_SIZE_MB, MY_DBAFS.BLOCK_ID BLOCK_HWM, MY_DBADF.BLOCKS MY_DBAFS.BLOCK_ID FAT_BLOCKS, FLOOR(((MY_DBADF.BLOCKS-MY_DBAFS.BLOCK_ID)*VALUE)/1024/1024) FAT_MB, CEIL(((MY_DBADF.BLOCKS*VALUE)/1024/1024 - ((MY_DBADF.BLOCKS-MY_DBAFS.BLOCK_ID)*VALUE)/1024/1024)) RESIZE_TO

FROM DBA_FREE_SPACE MY_DBAFS, DBA_DATA_FILES MY_DBADF, V$PARAMETER
WHERE V$PARAMETER.NAME = 'DB_BLOCK_SIZE' AND (MY_DBADF.TABLESPACE_NAME = UPPER('&&TABLE_SPACE') OR 'ALL' = UPPER('&&TABLE_SPACE') ) AND MY_DBAFS.TABLESPACE_NAME = MY_DBADF.TABLESPACE_NAME AND MY_DBAFS.FILE_ID = MY_DBADF.FILE_ID AND MY_DBAFS.BLOCK_ID = (SELECT MAX(BLOCK_ID) FROM DBA_FREE_SPACE WHERE FILE_ID = MY_DBAFS.FILE_ID)
ORDER BY FAT_BLOCKS DESC; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Useful Tips for Oracle 11g Invisible Indexes!
   Beware of using Oracle RAC with Data Warehouse!!
   Oracle – Availing The Assistance Of Wget For Patch Download
   Some Great Tips on Oracle 11g Encrypted Tablespaces!!