用科技创造明天

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