1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| 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'
/
spool chemin_fichier
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(+)
/
COLUMN cmd format a90 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
ORDER BY tablespace_name
/
spool off |
Partager