手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle undo表空间使用量为100%

[日期:2017-10-01] 来源:Linux社区  作者:lijiaman [字体: ]

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

image

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

image

在上一篇文章中,我们介绍了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表空间很空闲。

image

更多Oracle相关信息见Oracle 专题页面 http://www.chineselinuxuniversity.net/topicnews.aspx?tid=12

本文永久更新链接地址http://www.chineselinuxuniversity.net/Linux/2017-10/147232.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款