天萃荷净

Oracle DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常BUG

1.查询Oracle数据库版本

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视图查询Oracle数据文件大小

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视图查询查询Oracle数据文件大小

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研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle BUG DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/199.html

Oracle研究中心

关键词：

Oracle BUG

DBA_HIST_TBSPC_SPACE_USAGE查询undo表空间异常

Oracle bug7578292