MOS again unreliable-ORA-27163: outofmemory

Database Version: oracle

Database Version: oracle-11g @ xifenfei:/home/oracle $ sqlplus/as sysdbaSQL * Plus: Release 11.2.0.4.0 Production on Tue Sep 30 10:28:30 2014 Copyright (c) 1982,201 3, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition

Database Version

oracle -> 11g @xifenfei:/home/oracle$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 30 10:28:30 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for HPUX: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

Database patch information

oracle -> 11g @xifenfei:/home/oracle$opatch lspatches18973907;18795105;18701707;18284357;18020394;17564992;17308789;17306264;17259786;17079301;16477664;16188701;14368995;14245531;11744544;18522515;OCW Patch Set Update : 11.2.0.4.3 (18522515)18522509;Database Patch Set Update : 11.2.0.4.3 (18522509)

Collect spa reports reported ORA-27163: out of memory error

SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'errors','ALL') FROM dual;ERROR:ORA-27163: out of memoryORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211ORA-06512: at "SYS.DBMS_SQLPA", line 515ORA-06512: at line 1no rows selected

Collect logs after setting events

Connected.SQL> alter session set events '31156 trace name context forever, level 0x400';Session altered.SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'errors','ALL') FROM dual;DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TEST','HTML','ERRORS','ALL')--------------------------------------------------------------------------------   SQL Performance Impact Analyzer Report

Query MOS found XML Parser Fails With ORA-27163 (Out Of Memory) (Doc ID 1599434.1), related description:

According to the document description, this problem has been fixed in 11.2.0.4, but it still exists in hp unix. experience tells us that the current MOS cannot fully trust

• The temporary tablespace used by the lob field is not released after commit
• Analysis of Abnormal database logon events with $ sqlplus in sys User Password
• An ORA-31605 error occurred when using dbms_metadata.get_ddl
• Dblink causes scn detection changes
• Upgrade Oracle 10.2.0.x to 11.2.0.3
• Database Recovery Check script (Oracle Database Recovery Check)
• Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS
• An exception occurred while generating the awr html file SYS. DBMS_WORKLOAD_REPOSITORY in ORACLE 11.2.0.3.

Original address: MOS again unreliable-ORA-27163: out of memory, thanks to the original author to share.