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.
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。