Oracle undo表空间使用量为100%

244次阅读

共计 1913 个字符，预计需要花费 5 分钟才能阅读完成。

在 Toad 中发现 undo 表空间 undotbs1 使用量已经达到 100%，但是奇怪的是数据库并没有 hang 住，依然可以正常运转

Oracle undo 表空间使用量为 100%

通过 Oracle 提供的 EM 查看 undotbs1 表空间的使用，也达到了 78.8

Oracle undo 表空间使用量为 100%

在上一篇文章中，我们介绍了 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.CONTENTS
FROM 
    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