[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, 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表空间很空闲。