Oracle Error: ORA-00001: unique constraint (xxxxx) violated

Please follow the next solution in the future if EXPORT-IMPORT is used.

While the schema was exported with Export Data Pump, the application continued to insert data into the table. As Data Pump first exports the sequences, and later on the table data, it can occur that the nextval of the sequence as it is stored in the dumpfile is already used to insert data in the table at the time that the table data is exported.

To resolve this problem. please perform any of the following steps:

1. Run the export in consistent mode, meaning that the whole export is consistent to a given time (or System Change Number when using FLASHBACK_SCN).

i.e.
$ expdp system/<PASSWORD> directory=<MY_DIR> dumpfile=<DUMPFILE> logfile=<LOGFILE> reuse_dumpfiles=y schemas=IKB flashback_time=systimestamp

Note: To avoid ORA-01555 (snapshot too old) errors, ensure that the spfile parameter UNDO_RETENTION is high enough to cover the time it takes to run the Export Data Pump job on the source database,

i.e.
-- If the expdp job takes 3.5 hours, set UNDO_RETENTION TO 4 hours:
alter system set undo_retention=14400 scope=spfile sid='*';

-- Or: --

2. Avoid that application is used during the time of the Export Data Pump job.

-- Or: --

3. In the target database, increase the next value of the sequence by re-creating it.

i.e.
select max(dbkey) from IKB.IX_BP_JOB;

MAX(DBKEY)
------------
24

select IKB.IX_SEQ_BP_JOB.nextval from dual;

NEXTVAL
------------
22

set long 200
select dbms_metadata.get_ddl('SEQUENCE','IX_SEQ_BP_JOB','IKB') "DDL" from dual;

DDL
--------------------------------------------------------------------------------
CREATE SEQUENCE "IKB"."IX_SEQ_BP_JOB" MINVALUE 1 MAXVALUE 9999999999999999999999999
999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE

drop sequence "IKB"."IX_SEQ_BP_JOB" ;
create sequence "IKB"."IX_SEQ_BP_JOB" start with 25 increment by 1;

 

/Code:
Oracle Error: ORA-00001: unique constraint (APJDAADM.PK_LDLEG) violated
Oracle Error: ORA-00001: unique constraint (I2_TMS_635.STOP_I1) violated
 java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CCI.RDS_LOAD_RECORD_PK) violated
* constraint may vary

When SEQ.nextval is not greater than max(), the next value available from nextval may have been already used.

Take the Load Table for example, verify the following:
select max(LD_LEG_ID) from LD_LEG_T;
select LD_LEG_TSEQ.nextval from dual;

When the number returned by the second query is smaller than the number returned by the first, Oracle Error: ORA-00001 results.
 

 

It is common for nextval to be out-of-synch with the actual value being use after database import or export. The issue is not JDA product related. Usually a DBA can easily take care of it.

Also see Legacy Article ID 622429 (Article Number 000024259): Load ID jumps by 200.