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.
在进行插入操作时加入提示/*+append*/时可减少redo log的产生并且使数据直接追加到HighWaterMark后而使性能得到一定程度的提升,但/*+append*/会对表加排它锁,从而阻止表上的除select以外所有DML语句的操作,因此应慎用,注意应及时commit,否则会产生此错误。
测试脚本:
- SELECT * FROM v$version;
- BEGIN
- dbms_output.put_line('############### '||to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||' ###############');
-
- INSERT INTO t
- SELECT * FROM t WHERE ROWNUM<=100;
-
- UPDATE t SET ID=2000 WHERE ID=10000;
-
- COMMIT;
-
- dbms_output.put_line('1--OK');
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- dbms_output.put_line('-1--'||SQLERRM);
- END;
- /
- BEGIN
- dbms_output.put_line('############### '||to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||' ###############');
-
- INSERT /*+append*/ INTO t
- SELECT * FROM t WHERE ROWNUM<=100;
-
- UPDATE t SET ID=2000 WHERE ID=10000;
-
- COMMIT;
-
- dbms_output.put_line('1--OK');
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- dbms_output.put_line('-1--'||SQLERRM);
- END;
- /
- BEGIN
- dbms_output.put_line('############### '||to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||' ###############');
-
- INSERT /*+append*/ INTO t
- SELECT * FROM t WHERE ROWNUM<=100;
-
- COMMIT;
-
- UPDATE t SET ID=2000 WHERE ID=10000;
-
- COMMIT;
- dbms_output.put_line('2--OK');
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- dbms_output.put_line('-1--'||SQLERRM);
- END;
- /
运行结果:
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
- PL/SQL Release 11.2.0.1.0 - Production
- CORE 11.2.0.1.0 Production
- TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
- NLSRTL Version 11.2.0.1.0 - Production
- Executed in 0.046 seconds
- ############### 2012-12-19 17:45:46 ###############
- 1--OK
- PL/SQL procedure successfully completed
- Executed in 0 seconds
- ############### 2012-12-19 17:45:46 ###############
- -1--ORA-12838: cannot read/modify an object after modifying it in parallel
- PL/SQL procedure successfully completed
- Executed in 0 seconds
- ############### 2012-12-19 17:45:46 ###############
- 2--OK
- PL/SQL procedure successfully completed
- Executed in 0 seconds
- SQL>