Code to shrink Your Tablespaces

Use it at your own risk.


SET verify OFF
COLUMN file_name format a50 WORD_WRAPPED
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings format 999,990 heading "Poss.|Savings"
BREAK on REPORT
COMPUTE sum of savings on REPORT
COLUMN value new_val BLKSIZE
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size';

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 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 (+);

SELECT 'alter database datafile '''
||File_Name
||''' resize '
||Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024)
||'m;' cmd
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 (+)
AND Ceil(Blocks *&& blkSize / 1024 / 1024) - Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024) > 0;