[Oracle]undo表空间使用量为100%

在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,1)">COUNT(*) CNT,每个表空间数据文件的个数
    DECODE(SUM(DECODE(AUTOEXTENSIBLE,NO',0,1)">1)),1)">YES') AUTOEXT   是否自动扩展
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
)

 
    D.TABLESPACE_NAME,1)">round(NVL(A.BYTES / 1024 1024,1)">0),1)">1)                      all_tablespace_gb,1)">round(NVL(U.BYTES,1)">0) )                     undo_tablespace_used_gb,1)">- NVL(U.BYTES,1)">)    undo_tablespace_free_gb,1)">round(NVL(U.BYTES / A.BYTES * 100,1)">1)                           "undo_tablespace_used_per %",A.AUTOEXT,D.STATUS,A.CNT                                                              datafile_cnt,D.CONTENTS
 
    DBA_TABLESPACES D,
    DF              A,( 
         TABLESPACE_NAME,1)">SUM(BYTES) BYTES                              记录不可使用的undo segment:包括状态为active的区，不包括unexpired和expired
      DBA_UNDO_EXTENTS
     WHERE STATUS IN ()                        --如果使用了retention guarantee特性，则还需统计状态为unexpired的区：WHERE STATUS IN (‘ACTIVE’,’UNEXPIRED’)                   
     BY TABLESPACE_NAME) U     
WHERE 
    D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)  左外连接(left join)
AND= U.TABLESPACE_NAME(+)
 
    D.CONTENTS UNDO'
ORDER BY 1

通过这种方法，我们可以统计出实际使用的undo表空间（extent状态为ACTIVE的部分），可以发现undotbs1表空间很空闲。

[Oracle]undo表空间使用量为100%

猜你在找的Oracle相关文章