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

    Oracle 11g : Sequence Issues after impdp

    RK334
      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