insert table error

[Roshan]

Oracle Database 12.1.0.2

HI Erman,

I am getting error below while inserting in a table

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @createtab.sql

584296117 rows created.

INSERT /*+ APPEND */ INTO MOB_DAT_TMP2 SELECT WAY,VOLIN,IMEI,VOLOUT,CELLID,CHARGE,POSTPRE,DURATION,TIME,DATADATE,MSISDN FROM ARCHICOM.MOBILE_DATA PARTITION(MOBILE_DATA_2018_03)
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


INSERT /*+ APPEND */ INTO MOB_DAT_TMP2 SELECT WAY,VOLIN,IMEI,VOLOUT,CELLID,CHARGE,POSTPRE,DURATION,TIME,DATADATE,MSISDN FROM ARCHICOM.MOBILE_DATA PARTITION(MOBILE_DATA_2018_04)
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


INSERT /*+ APPEND */ INTO MOB_DAT_TMP2 SELECT WAY,VOLIN,IMEI,VOLOUT,CELLID,CHARGE,POSTPRE,DURATION,TIME,DATADATE,MSISDN FROM ARCHICOM.MOBILE_DATA PARTITION(MOBILE_DATA_2018_05)
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Thanks to advise.

Regards,

Roshan

[Roshan]

I checked the docs,

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1

Error:  ORA-12838 cannot read/modify an object after modifying it in parallel
---------------------------------------------------------------------------
Cause:  Within the same transaction, an attempt was made to add read or
        modification statements on a table after it had been modified in
        parallel or with direct load. This is not permitted.
Action: Rewrite the transaction, or break it up into two transactions: one
        containing the initial modification and the second containing the
        parallel modification operation.

Should I remove the APPEND?

[ErmanArslansOracleBlog]

Fact1:

A transaction can contain multiple parallel DML statements that modily
different tables, but after a parallel DML statement modifies a table,
NO subsequent serial or Parallel statment (DML or QUERY) can access the
same table again in that transaction.

Fact2:

Each insert in SQL*plus is considered a transaction, while the whole
PL/SQL BLOCK is considered a transation.

Probably, these inserts are done in a PLSQL block.. (in that script, there is probably a PLSQL block and the inserts are coming from that block)

So, if it is a PLSQL block, then this error is normal.
In that PL/SQL Procedure, just insert a commit after the 1st DML statement prior to the 2nd DML statement (after 2nd tprioer 3rd and so on )on the same table in the same transaction.

[Roshan]

Thanks