分析表空間空閒率並收縮表空間

hooca發表於2015-04-22
查詢表空間空閒率：


點選(此處)摺疊或開啟

  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

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章