-
Title
Post process stops due to "ORA-02091 and ORA-00001" -
Description
Post process tries to commit the transactions and it is stops due to errors ORA-02091 and ORA-00001.
Event_log has the following error messages:
[11] 12/21/08 08:51 Error: 12070 - OCITransCommit failed with ORA-2091. [sp_opst_mt(osp)/2293]
[11] 12/21/08 08:51 Error: failed to commit transaction session 33 in CommitFunc(): ORA-02091: transaction rolled back.ORA-00001: unique constraint <unique constraint name> violated. Will exit Post.. [sp_opst_mt (<post queue name>)/2293]
12/21/08 08:51 Process exited sp_opst_mt (<post queue name>) [pid = 2293] - exit(1) -
Cause
Unique constraints are set as deferred unique constraints.
In the case of a deferred unique constraint, when a unique constraint violation (ORA-00001) occurs, Oracle will not return ORA-00001 right away but it will wait until the commit. Then it returns a combination of ORA-02091 and ORA-00001.
As a result, it causes Post process to fail due to the error.
-
Resolution
Modify constraints from deferred constraints to (immediate) constraints.
-
Additional Information
To test :
create table test_with_deferred
2 (
3 ID number(38)
4 );
Table created.
alter table test_with_deferred add constraint test_const unique (ID) deferrable initially deferred ;
Table altered.
insert into test_with_deferred values (1);
1 row created.
insert into test_with_deferred values (1);
1 row created.
commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (scott.test_const) violated