[Oracle]undo資料表空間使用量為100%

標籤：one   nts   free   資料   左外串連   _id   查看   undo   color   

在Toad中發現undo資料表空間undotbs1使用量已經達到100%，但是奇怪的是資料庫並沒有hang住，依然可以正常運轉

通過Oracle提供的EM查看undotbs1資料表空間的使用，也達到了78.8

在上一篇文章中，我們介紹了undo資料表空間中區的3種狀態：ACTIVE、EXPIRED、UNEXPIRED。在對其概念理解後，個人認為在未設定undo資料表空間retention guarantee的情況下，只要ACTIVE狀態的區未達到100%，皆不會造成資料庫hang住。

那麼Toad中undotbs1為什麼使用率達到100%，而EM為78.8呢？

通過追蹤Toad及EM的對應SQL語句，發現Toad把3種狀態都計算在計算在內了，而EM則計算了ACTIVE和EXPIRED兩種狀態。

通過查詢狀態為ACTIVE的區，發現數量為0:

SQL> select * from dba_undo_extents where status = ‘ACTIVE‘;OWNER SEGMENT_NAME  TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO COMMIT_JTIME COMMIT_WTIME         STATUS----- ------------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ------------ -------------------- ---------

所以，可以認為：

1.Toad統計的undo資料表空間的使用率是不準確的；

2.EM統計了2種狀態，個人認為在未設定undo資料表空間retention guarantee的情況下，只要統計ACTIVE狀態的區即可，統計undo資料表空間使用率的SQL語句如下：

--統計ACTIVE狀態的區佔用率多少undo資料表空間

WITH DF AS(SELECT     TABLESPACE_NAME,    SUM(BYTES) BYTES,    COUNT(*) CNT,                                                            --每個資料表空間資料檔案的個數    DECODE(SUM(DECODE(AUTOEXTENSIBLE, ‘NO‘, 0, 1)), 0, ‘NO‘, ‘YES‘) AUTOEXT   --是否自動擴充 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)SELECT     D.TABLESPACE_NAME,    round(NVL(A.BYTES / 1024 / 1024 / 1024, 0),1)                      all_tablespace_gb,        round(NVL(U.BYTES, 0) / 1024 / 1024 / 1024 ,1)                     undo_tablespace_used_gb,     round(NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024 / 1024,1)    undo_tablespace_free_gb,    round(NVL(U.BYTES / A.BYTES * 100, 0),1)                           "undo_tablespace_used_per %",    A.AUTOEXT,    D.STATUS,                                        A.CNT                                                              datafile_cnt,    D.CONTENTSFROM     DBA_TABLESPACES D,               DF              A,               (SELECT          TABLESPACE_NAME,          SUM(BYTES) BYTES                              --記錄不可使用的undo segment:包括狀態為active的區，不包括unexpired和expired     FROM DBA_UNDO_EXTENTS     WHERE STATUS IN (‘ACTIVE‘)                        --如果使用了retention guarantee特性，則還需統計狀態為unexpired的區：WHERE STATUS IN (‘ACTIVE’,’UNEXPIRED’)                        GROUP BY TABLESPACE_NAME) U     WHERE     D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)  --左外串連(left join)AND     D.TABLESPACE_NAME = U.TABLESPACE_NAME(+)AND     D.CONTENTS = ‘UNDO‘ORDER BY 1

通過這種方法，我們可以統計出實際使用的undo資料表空間（extent狀態為ACTIVE的部分），可以發現undotbs1資料表空間很空閑。

[Oracle]undo資料表空間使用量為100%