Shrink Oracle DBF Files

SQL commands for finding block size and size of all tablespace files in an Oracle database. THe script produces the necessary commands to reduce the disk space used in the related dbf files.

select value from v$parameter where name = 'db_block_size'

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';
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;