0

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

10
  • It would help if you posted the exact error you get. Commented Aug 5, 2015 at 18:43
  • @ypercube added. what else would help you?
    – tesla747
    Commented Aug 5, 2015 at 18:53
  • 1
    How is USER1.HIGHT_PK defined? Is it the same definition in both databases? Commented Aug 5, 2015 at 18:56
  • 1
    It's hard to tell without any details about what the application is doing and queries, so I'm just guessing. I had such errors when data was synchronized, but sequences weren't (and sequences were used to generate values for primary keys)
    – a1ex07
    Commented Aug 5, 2015 at 19:05
  • 1
    See if this helps: stackoverflow.com/questions/13169375/…
    – mustaccio
    Commented Aug 5, 2015 at 19:49

2 Answers 2

2

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.

1
  • ok let me try an expdp with consistent parameter and re-import it once again.. btw, if I drop and recreate the constraints will that work??
    – tesla747
    Commented Aug 6, 2015 at 1:21
0

The last_number column in all_sequences was different in both the databases (11g and 12c) ideally it should be the same. What I did was I incremented the sequnce number of 12c database higher than the sequence number as that of 11g database. And the error did not show up. All is well.!

1
  • that is that what I described as case 2)
    – miracle173
    Commented Aug 6, 2015 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.