ORA-12838: cannot read/modify

insert插入数据时报错：

SCOTT@prod>insert /*+ append */ into scott.employee select * from scott.employee ;3584 rows created.SCOTT@prod>/insert /*+ append */ into scott.employee select * from scott.employee                                *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallel

查看表并行度

SQL> select a.degree from dba_tables a where a.table_name='EMPLOYEE';DEGREE-----------1

查看锁信息

SELECT o.object_name,       o.owner,       l.locked_mode,       s.sid,       s.serial#,       s.logon_time  FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id　 = o.object_id   AND l.session_id = s.sid   AND o.object_name = 'EMPLOYEE';   OBJECT_NAME      OWNER   LOCKED_MODE        SID    SERIAL# LOGON_TIME---------------- ------- ----------- ---------- ---------- -----------EMPLOYEE         SCOTT             6         80       5328 2019/11/24

ora-12838错误

SCOTT@prod>!oerr ora 1283812838, 00000, "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.

append会在高水位之上插入数据并且生成较少的redo数据，加快速插入速度的同时会持有一个排它锁，对于执行后未提交的事务容易引发该问题。

MOS相关文档

ORA-12838 with Direct Load Inserts (文档 ID 116494.1)PARALLEL RESTRICTIONS:A transaction can contain multiple parallel DML statements that modilydifferent tables, but after a parallel DML statement modifies a table,NO subsequent serial or Parallel statment (DML or QUERY) can access thesame table again in that transaction.***Each insert in SQL*plus is considered a transaction, while the wholePL/SQL BLOCK is considered a transation.