DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常BUG

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常BUG

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2.查询DBA_DATA_FILES视图

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION 
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(MAXBYTES)/1024 
--------------- ------------------ 
EXAMPLE                   33554416     
OGG                        5242880    
SYSAUX                    33554416       
SYSTEM                    33554416        
TEMP                      33554416        
TS_INDEX_BASE             15728640     
TS_PUB_BASE               15728640        
UNDOTBS1                  33554416       
USERS                     33554416       

9 rows selected.

3.查询DBA_HIST_TBSPC_SPACE_USAGE视图

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;

NAME            TABLESPACE_MAXSIZE 
--------------- ------------------ 
EXAMPLE                    4194302    
OGG                         655360      
SYSAUX                     4194302         
SYSTEM                     4194302       
TEMP                       4194302         
TS_INDEX_BASE              1966080         
TS_PUB_BASE                1966080          
UNDOTBS1                   8388604         
USERS                      4194302        

9 rows selected.

观察者两个视图的运行结果,DBA_HIST_TBSPC_SPACE_USAGE视图收集到的统计大小和实际大小都存在一定的误差,但是UNDO表空间出入太明显(UNDOTBS1),特别是最大值和当前值,几乎是真实大小的两倍

4.排除原因
4.1)收集信息是否是最新

SQL> select MAX(rtime) FROM DBA_HIST_TBSPC_SPACE_USAGE;

MAX(RTIME)
-------------------------
01/09/2012 15:00:50

4.2)statistics_level是否被设置为basic

SQL> show parameter statistics_level;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

通过这两个查询证明,收集信息和statistics_level都是符合要求,那么为什么undo空间的空间信息还是正常的两倍呢?

5.怀疑bug,查询mos

6.查询11.2.0.3中DBA_HIST_TBSPC_SPACE_USAGE是否正常

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;


NAME            TABLESPACE_MAXSIZE 
--------------- ------------------ 
DRSYS_1                    4194302    
EXAMPLE                    4194302    
ODU                        8139262      
SYSAUX                     4194302      
SYSTEM                     4194302        
TEMP                       4194302       
TEST_OCP                   4194302         
UNDOTBS01                  3938560        
USERS                      4194302         

9 rows selected.

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024  3        
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION 
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(MAXBYTES)/1024 
--------------- ------------------ 
DRSYS_1                   33554416     
EXAMPLE                   33554416     
ODU                       65114096     
SYSAUX                    33554416       
SYSTEM                    33554416         
TEMP                      33554416      
TEST_OCP                  33554416         
UNDOTBS01                 31457280      
USERS                     33554416      

9 rows selected.

通过对比,发现基本误差不大,确定在该版本,bug7578292已经被修复

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>