当前位置:Oracle研究中心 > 故障案例 >
时间:2016-05-18 22:02 来源: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
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.
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
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
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。