This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 11, 2013 7:12 AM by RK334 RSS

Oracle 11g : Sequence Issues after impdp

RK334 Newbie
Currently Being Moderated
Hi All,

We are migrating from Oracle 10g to Oracle 11g. As part of this, we take a expdp from 10g and successfully did an import using impdp on to Oracle 11g database.

But the problem is, there were few Primary key violations which occurred and all of them relate to sequences.

The Maximum data in the tables and the last number in the sequences differed which caused the issue.

Upon investigation and browsing the Web, http://www.nerdliness.com/article/2009/03/18/my-oracle-sequencedatapump-shenanigans ; I understood it could be because of the export taken while the application is online and writing to the database.

I reset all the failing sequences manually and it is fine now.

My questions now are

*1) Can we ascertain that the export taken when the source database is offline would eliminate the sequences issue.*

2) As this is being done in Production, I would like to make few checks to ensure that the sequences are properly imported. ---

Again reading few websites and oracle Forums, i found the below sql's..

select table_name, column_name, utl_raw.cast_to_number(high_value) as highval
from dba_Tab_columns
where owner = 'PRODUCTION_OWNER'
AND DATA_TYPE= 'NUMBER'
AND (OWNER, TABLE_NAME, COLUMN_NAME) IN
(SELECT CC.OWNER, CC.TABLE_NAME, CC.COLUMN_NAME
FROM DBA_CONS_COLUMNS CC
JOIN DBA_CONSTRAINTS C
ON CC.OWNER=C.OWNER
AND CC.CONSTRAINT_NAME=C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE ='P'
)
ORDER BY 3;


SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, LAST_NUMBER
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER = 'PRODUCTION_OWNER'
ORDER BY LAST_NUMBER

If I relate the last_number with the highval and if they are same, does that mean the sequences are imported properly.

Note: We have sequence caching done and we are on RAC.

Edited by: ramakrishnavydyula on Jan 30, 2013 9:36 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points