MERGE to table with Virtual Columns + Error Log Table = ORA-03113

This is just in case you come across this. It appears to be an Oracle bug. I don’t know if it’s been fixed in a later release. I haven’t isolated a simple repeatable test case yet, so I don’t know if the scenario here includes all the relevant details or not.

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

Scenario:

  • MERGE into a table that has one or more virtual columns.
  • The MERGE statement has a “LOG ERRORS” clause.
  • The error log table is a standard one created using DBMS_ERRLOG.create_error_log.

Other factors that may or may not be involved are:

  • the target table is in a different schema
  • the merge statement queries another table with the same name in the current schema
  • the merge statement includes WHEN MATCHED THEN UPDATE, DELETE WHERE, WHEN NOT MATCHED THEN INSERT cluases
  • the merge statement has a WHERE clause on the WHEN NOT MATCHED clause

Result:

ORA-03113: end-of-file on communication channel and/or ORA-03114: not connected to ORACLE

This happens whether the MERGE is executed or just explain-plan’ed – so it shouldn’t be related to the actual data in either the source or destination tables.

Workaround:

After creating the log table, but before the merge, drop the columns from the error log that are virtual columns in the target table.

Jeffrey Kemp 1 September 2011

bug / problem-solved / SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Quick and simple frequency analysis
Installing Oracle 11gXE on Amazon Elastic Cloud

Comments

  1. Rob van Wijk
    1 September 2011 - 3:36 pm

    Hi Jeff,

    When you get a ORA-03113, the server process has dumped its error message – the real one – into the alert file. Did you check that error message yet?

    Regards,
    Rob.

    • Jeffrey Kemp
      1 September 2011 - 3:53 pm

      No I didn’t 🙂 but I did now.

      I did find these:

      ORA-07445: exception encountered: core dump [qeselInsPre()+674] [SIGSEGV] [ADDR:0x6] [PC:0x1D606FA] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [qeselInsPre()+674] [SIGSEGV] [ADDR:0x6] [PC:0x1D606FA] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [qeselInsPre()+674] [SIGSEGV] [ADDR:0x6] [PC:0x1D606FA] [Address not mapped to object] []

  2. Rob van Wijk
    1 September 2011 - 3:55 pm

    It doesn’t dump the 3113 error message, if I recall correctly. That’s the client error message. I always search with the time the error occurred.

    • Jeffrey Kemp
      1 September 2011 - 4:01 pm

      Ok, I found this in the alert log:

      Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x1D606FA, qeselInsPre()+674] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/dev11g01/dev11g01/trace/dev11g01_ora_31179.trc  (incident=32953):
ORA-07445: exception encountered: core dump [qeselInsPre()+674] [SIGSEGV] [ADDR:0x6] [PC:0x1D606FA] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/dev11g01/dev11g01/incident/incdir_32953/dev11g01_ora_31179_i32953.trc

      The first trace file mentioned has this:

      Trace file /u01/app/oracle/diag/rdbms/dev11g01/dev11g01/trace/dev11g01_ora_31179.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1_ent
System name:    Linux
Node name:      redacted
Release:        2.6.18-164.6.1.0.1.el5
Version:        #1 SMP Tue Nov 3 19:09:14 EST 2009
Machine:        x86_64
Instance name: dev11g01
Redo thread mounted by this instance: 1
Oracle process number: 64
Unix process pid: 31179, image: oracle@redacted


*** 2011-09-01 11:01:02.864
*** SESSION ID:(48.46754) 2011-09-01 11:01:02.864
*** CLIENT ID:() 2011-09-01 11:01:02.864
*** SERVICE NAME:(dev11g01.redacted) 2011-09-01 11:01:02.864
*** MODULE NAME:(SQL*Plus) 2011-09-01 11:01:02.864
*** ACTION NAME:() 2011-09-01 11:01:02.864

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x6] [PC:0x1D606FA, qeselInsPre()+674] [flags: 0x0, count: 1]

*** 2011-09-01 11:01:04.891
Incident 32953 created, dump file: /u01/app/oracle/diag/rdbms/dev11g01/dev11g01/incident/incdir_32953/dev11g01_ora_31179_i32953.trc
ORA-07445: exception encountered: core dump [qeselInsPre()+674] [SIGSEGV] [ADDR:0x6] [PC:0x1D606FA] [Address not mapped to object] []

ssexhd: crashing the process…
Shadow_Core_Dump = PARTIAL

      The second trc file mentioned is too large to include here…

  3. Rob van Wijk
    1 September 2011 - 6:36 pm

    At least you now know for sure it’s a bug :-). I’ve encountered many times a regular ORA-message in the alert file which could be solved by some code changes (especially when doing XML in Oracle9), but that’s not the case here.

  4. yuannek29
    7 December 2011 - 3:43 am

    I ran across the same thing on Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production.

    I can also say that none of the other factors you mention were involved in my case.

    Other factors that may or may not be involved are:

    the target table is in a different schema
    the merge statement queries another table with the same name in the current schema
    the merge statement includes WHEN MATCHED THEN UPDATE, DELETE WHERE, WHEN NOT MATCHED THEN INSERT cluases
    the merge statement has a WHERE clause on the WHEN NOT MATCHED clause

  5. Clyde
    17 September 2012 - 10:17 pm

    Thank you Jeff!

    Good man. Great job describing the issue and providng a solution.

    Much Appreciated!

Leave a Reply

Your email address will not be published / Required fields are marked *