分析表空间空闲率并收缩表空间

查询表空间空闲率：

点击(此处)折叠或打开

1. COL TBS_NAME FORMAT A15
   
2. COL USAGE_RATE FORMAT A10
   
3. SELECT Upper(F.TABLESPACE_NAME) "TBS_NAME",
   
4.        D.TOT_GROOTTE_MB "TBS_MB",
   
5.        D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USAGE_MB",
   
6.        To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
   
7.        || '%' "USAGE_RATE",
   
8.        F.TOTAL_BYTES "FREE_SPACE_MB",
   
9.        F.MAX_BYTES "MAX_BLOCK_SIZE_MB"
   
10. FROM (SELECT TABLESPACE_NAME,
    
11.                Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
    
12.                Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
    
13.         FROM SYS.DBA_FREE_SPACE
    
14.         GROUP BY TABLESPACE_NAME) F,
    
15.        (SELECT DD.TABLESPACE_NAME,
    
16.                Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
    
17.         FROM SYS.DBA_DATA_FILES DD
    
18.         GROUP BY DD.TABLESPACE_NAME) D
    
19. WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    
20. ORDER BY 4 DESC;

TBS_NAME            TBS_MB   USAGE_MB USAGE_RATE FREE_SPACE_MB MAX_BLOCK_SIZE_MB
--------------- ---------- ---------- ---------- ------------- -----------------
SYSTEM                 740     738.19   99.76%            1.81              1.81
SYSAUX                 540     512.94   94.99%           27.06             27.06
USERS                    5       1.31   26.20%            3.69              3.69
UNDOTBS1                90      15.31   17.01%           74.69                53

得出收缩表空间的命令

点击(此处)折叠或打开

1. COL CMD FORMAT A80
   
2. 
   
3. select 'alter database datafile ''' || a.file_name || ''' resize ' ||
   
4. round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;' AS CMD,
   
5. a.filesize || 'M' as "TOTAL_SIZE",
   
6. c.hwmsize || 'M' as "HW"
   
7. from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
   
8. from dba_data_files) a,
   
9. (select file_id, round(max(block_id) * 8 / 1024) as HWMsize
   
10. from dba_extents
    
11. group by file_id) c
    
12. where a.file_id = c.file_id
    
13. and a.filesize - c.hwmsize > 100;

CMD                                                                    TOTAL_SIZE    HW   
alter database datafile '/KBACH1T/data/logdata01.dbf' resize 9427M;    30000M        4184M

TOTAL_SIZE表示当前占用空间
HW表示实际有效利用的空间

接下来执行CMD命令即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1590434/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1590434/