ORA-12838: cannot read/modify an object after modifying it in parallel

 今天在测试一个parallel实验的时候遇到报错，比较纳闷

ORA-12838: cannot read/modify an object after modifying it in parallel

后来才明白为什么会报错

SQL> insert /*+ append*/ into t1 select * From t2;
71899 rows created.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
 0  recursive calls
370  db block gets
347  consistent gets
343  physical reads
    2822944  redo size
       1116  bytes sent via SQL*Net to client
       1292  bytes received via SQL*Net from client
 4  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
      71899  rows processed

查看报错的原因：

[oracle@rac1 ~]$ 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.

看了解释以后还是不明白，去网上看了下前人对这个错误的解释

insert /*+ append*/ into t1 select * From t2;

SQL> select * From v$lock where sid=54;

ADDR KADDR SID TY        ID1  ID2  LMODE    REQUEST CTIME   BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000974599B8 0000000097459A10  54 AE        100   0       4 0  15610
0000000097459F68 0000000097459FC0  54 TO       5003   1       3 0  13850
00002B6E0A7FD0D0 00002B6E0A7FD130  54 TM      73443    0      6  0  339 0
00002B6E0A7FD0D0 00002B6E0A7FD130  54 TM       5003   0       3 0   3360
0000000095C9A058 0000000095C9A0D0  54 TX     327705 997       6 0   3390

和普通的insert 对比 lock锁

SQL> insert into t1 select * From t2;
SQL> select * from v$lock where sid=57 ;
ADDR KADDR SID TY        ID1  ID2  LMODE    REQUEST CTIME   BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000097459C28 0000000097459C80  57 AE        100   0       4 0   1230
00002AC39A2CE3E8 00002AC39A2CE448  57 TM      73443    0      3  0   51 0
0000000095D16C98 0000000095D16D10  57 TX     262163 640   6  0   51 0
如上述： 

insert /*+ append*/ into t1 select * From t2; 表上有 TM 有个6号锁，排他，必须commit or roll 后才能查看数据。而普通的insert tm上只是3号共享锁

结论：

insert /*+ append*/ into t1 select * From t2;

这样的insert 语句写法是不正确的写法，会产生大量的undo，而且insert后会在表级加上6号锁，在当前session里是不能查询，也不能再一次的插入数据，进行dml操作，否则  会报ORA-12838: cannot read/modify an object after modifying it in parallel错误。

正确的append 写法是这样，

                                  alter table  t1 nologging;

SQL> insert /*+ append parallel(2)*/ into t1 select * From t2;

并且在使用append 快速加载数据完成后，需要commit or roll ，才能进行进行对该表进程操作