联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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已经被修复