Post date: 01-Jun-2009 10:24:22
column cmd format a75 word_wrapped
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