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?12838
12838,?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.