今天在测试一个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 ,才能进行进行对该表进程操作