ORA-03106: fatal two-task communication protocol error

I got ” ORA-03106: fatal two-task communication protocol error ”  error in Oracle database.

ORA-03106: fatal two-task communication protocol error

Details of error are as follows.

ORA-03106 fatal two-task communication protocol error

Cause: The communication path between Oracle and the user task has stopped. This is an internal error message not usually issued.

Action: Contact Oracle Support Services.

On 12.1.0.2, an INSERT statement succeeds in inserting rows yet generates an ORA-3106 error:

SQL> TRUNCATE table target_table;

Table truncated.

SQL> -- Table has NO rows
SQL> SELECT * FROM target_table;

no rows selected

SQL>
SQL> INSERT INTO target_table (id)
  2  SELECT t.id
  3  FROM (
  4        SELECT 1 id
  5             , XMLQUERY('/*/LIST_ITEMS' PASSING XMLTYPE('<ROOT><LIST_ITEMS><LIST_ITEM/></LIST_ITEMS></ROOT>') RETURNING CONTENT)
  6        FROM dba_tables
  7        WHERE rownum = :rw
  8        UNION ALL
  9        SELECT 2 id
 10             , XMLQUERY('/*/LIST_ITEMS' PASSING XMLTYPE('<ROOT><LIST_ITEMS><LIST_ITEM/></LIST_ITEMS></ROOT>') RETURNING CONTENT)
 11        FROM dual
 12        WHERE rownum = :rw
 13       ) t;
INSERT INTO target_table (id)
*
ERROR at line 1:
ORA-03106: fatal two-task communication protocol error

SQL> -- Despite the ORA-3106, rows were actually inserted
SQL> SELECT * FROM target_table;

     ID
-------
      1
      2

fatal two-task communication protocol error

This ORA-03106 error is related to the communication path between Oracle and the user task has stopped. This is an internal error message not usually issued.

Contact Oracle Support Services.

(Unpublished) Bug.20319808 – 47294: ORA-03106 WHILE UPDATING OR AND CSX COLUMN, BUT THE UPDATE IS SUCCESSFUL

Workaround:

Ignore the error:

SQL> -- Despite the ORA-3106, rows were actually inserted
SQL> SELECT * FROM target_table;

     ID
-------
      1
      2



Or, place the INSERT SELECT statement within a PLSQL block, see below:

SQL> TRUNCATE table target_table;

Table truncated.

SQL>
SQL> SELECT * FROM target_table;

no rows selected

SQL>
SQL> begin
  2   INSERT INTO target_table (id)
  3   SELECT t.id
  4   FROM (
  5         SELECT 1 id
  6              , XMLQUERY('/*/LIST_ITEMS' PASSING XMLTYPE('<LIST_ITEMS><LIST_ITEM/>') RETURNING CONTENT)
  7         FROM dba_tables
  8         WHERE rownum = :rw
  9         UNION ALL
 10         SELECT 2 id
 11              , XMLQUERY('/*/LIST_ITEMS' PASSING XMLTYPE('<LIST_ITEMS><LIST_ITEM/>') RETURNING CONTENT)
 12         FROM dual
 13         WHERE rownum = :rw
 14        ) t;
 15 end;
 16 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM target_table;

  ID
----------
  1
  2

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )