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

所以，可以认为：

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

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

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

标签：one   nts   free   数据   左外连接   _id   查看   undo   color