Resize Datafile to Optimal Size

SQL> Define BLKSIZE=

SELECT /*+rule*/ 'ALTER DATABASE DATAFILE '''||FILE_NAME||''' RESIZE '||CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )||'M;',

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 A,

( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM

FROM DBA_EXTENTS

GROUP BY FILE_ID ) B

WHERE A.FILE_ID = B.FILE_ID(+)

OUTPUT 'ALTERDATABASEDATAFILE'''||FILE_NAME||'''RESIZE'||CEIL((NVL(HWM,1)*8192)/1024/1024)||'M;' SMALLEST CURRSIZE SAVINGS ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\SYSTEM01.DBF' RESIZE 684M; 684 690 6 ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\SYSAUX01.DBF' RESIZE 466M; 466 490 24 ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\UNDOTBS01.DBF' RESIZE 89M; 89 90 1 ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\USERS01.DBF' RESIZE 5M; 5 5 0 ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\EXAMPLE01.DBF' RESIZE 82M; 82 100 18