11.2.0.3 下尝试使用11g health monitor新特性时出现了ORA-00604、ORA-01427, 查询MOS发现 (Bug 12385172: ORA-01427 WHEN EXECUTING DBMS_HM.RUN_CHECK),当 DB中存在case when then的function index时会触发该BUG:
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> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com SQL> exec dbms_hm.run_check('Dictionary Integrity Check','check-2'); BEGIN dbms_hm.run_check('Dictionary Integrity Check','check-2'); END; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYS.DBMS_HM", line 191 ORA-06512: at line 1可以通过以下脚本找出 DB中case when then类型的函数索引:
-- Determine DDL statements (note: this will take a while to return results!) set long 100000 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false); exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false); -- Checking the DDL statement col DDL form a100 word_wrapped select dbms_metadata.get_ddl(RTRIM(UPPER(object_type)),
版权声明:原创作品,如需转载,请注明出处。否则将追究法律责任
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1278313