«
»

缩小 Oracle 表空间

———– maxshrink.sql ———————————-

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
/

Tags: ,

This entry was posted on 星期一, 十一月 24th, 2008 at 下午 1:43 and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply