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 )

 

 587 views last month,  5 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply