Unique constraint error Oracle

exportimportoracleoracle-12cunique-constraint

I have two databases – one is 11g and another 12c. 12c db has the exact data of 11g (expdp/impdp). Now I'm facing an error in one of the tables with unique constraint error. I have checked the structure of the table and constraints enabled for the table in both the databases, that is same as well. Now how can I sort this out? I'm facing this error on the application which is running on it, when I click one of the tabs.
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001:unique constraint (USER1.HIGHT_PK) violated is the error i'm getting

Best Answer

You wrote

12c db has the exact data of 11g (expdp/impdp)

But are you sure?

1) Did you make a consistent export of your tables? This means all table data is from the same point in time. For this it is sufficient that you stop all other write activity on the database during export. Or you set the flashback_scn parameter when using expdp or the consistent parameter when using exp.

2) Are your sequence value consistent with your table data? This means all table data and the state of the sequence is from the same point in time. For this it is sufficient that you stop all other read and write activity on the database during export. This is the only way i know to guarantee this consistency. I think that enen the flashback_scn or consistent parameters can't help here. They are useful for for transactional data only. I think the structures like sequences are extracted before the table data is read, here is the log from an export https://community.oracle.com/thread/3519031

> expdp system/system123 directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only   


Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 20 February, 2014 7:55:03  


Copyright (c) 2003, 2007, Oracle.  All rights reserved.  


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_16":  system/******** directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only   
Processing object type SCHEMA_EXPORT/USER  
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT  
Processing object type SCHEMA_EXPORT/ROLE_GRANT  
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE  
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA  
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA  
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM  
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC  
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE  
Processing object type SCHEMA_EXPORT/TABLE/TABLE  
(...)

Solution

If you didn't make a data consistent export (case 1) you should repeat the export with the appropriate [consistency parameter]. If you have a problem with the sequences (case 2) you can try the following. Recreate the sequences of the target database (where you have imported the schema) like the current sequences of the source database. Now all sequences produce values that weren't yet used by the application. Most application have no problems (should not have problems) if there are gaps in the series of sequence numbers.

Related Question