A>insert /*+append*/ into t1 select * from dba_users;
15 rows created.
insert /*+append*/ into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
可以看出报错是在第二次使用/* +APPEND */
A>!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.
查了下错误代码，说同一个事务中，对同一个表进行过并行操作，或者直接加载后，就不能再进行读写操作了
分析一下
1.从直觉判断，错误与同一个事务没有多大关系
2.APPEND是直接加载没错，但没听说过直接加载后不能再进行读写操作了
对报错行为概括一下就是：相同操作，第一次可以，第二次不行
网上google一下，发现与锁有关，简单地说：第一次以APPEND方式插入后，在表T1上加了6号排它锁，所以第二次插入时失败
具体实验一下
A>insert into t1 select * from dba_users;
15 rows created.
A>select sid,type,id1,id2,lmode,request,block from v$lock where sid=1;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
1 AE 100 0 4 0 0
1 TM 59051 0 3 0 0 //常规插入时加的是3号锁
1 TX 262175 501 6 0 0
A>insert /*+append*/ into t1 select * from dba_users;
15 rows created.
A>select sid,type,id1,id2,lmode,request,block from v$lock where sid=1;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
1 AE 100 0 4 0 0
1 TM 59051 0 6 0 0 //改成APPEND插入后变成了6号锁
1 TX 262175 501 6 0 0
此后无论是常规插入，APPEND插入，还是更新删除操作，一律失败，原因都是6号排它锁
A>insert /*+append*/ into t1 select * from dba_users;
insert /*+append*/ into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
A>insert into t1 select * from dba_users;
insert into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
A>update t1 set user_id=1;
update t1 set user_id=1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
A>delete from t1;
delete from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
最后要注意的就是hint的写法，其中多一个空格也不行，尤其是在+前面
A>insert into t1 select * from dba_users;
15 rows created.
A>insert /* +append */ into t1 select * from dba_users; //+append前后都有空格，没问题
15 rows created.
A>insert /* +append*/ into t1 select * from dba_users; //+append前面有空格，后边没空格，没问题
15 rows created.
A>insert /*+append */ into t1 select * from dba_users; //+append前面没空格，后面有，不行了，锁变成6号了，换句话说，这种写法可能才是正确
15 rows created.
A>insert /*+append*/ into t1 select * from dba_users; //当然了，+append前后都没有，也是加的6号锁
insert /*+append*/ into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
总结
关于APPEND有两点要注意：1.使用/*+APPEND*/后会在表上加6号排它锁
2.注意/*+APPEND*/中，/*和+号之间不能有空格，否则就不是APPEND方式了
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29646619/viewspace-1163324/ ，如需转载，请注明出处，否则将追究法律责任。
