ORACLE报出错误如下:
ORA-12838: cannot read/modify an object after modifying it in parallel
解释如下:
ORA-12838 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
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可以实现直接路径加载,速度比常规加载方式快。但有一点需要注意: insert append时在表上加“6”类型的锁,会阻塞表上的所有DML语句。因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据,并不一定会提高速度。因为每一时刻只能有一个进程在加载(排它锁造成)。
我先执行了一个delete语句,又执行了insert append. 这个delete语句并没有造成后面的insert append报错。但在我执行了一个insert append之后,再执行任何DML语句都会报错。
所以在业务满足的条件下,执行玩append后应立即提交或者回滚。