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. 

在进行插入操作时加入提示/*+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>