1 Reply Latest reply: Jan 31, 2007 7:12 AM by 277993 RSS

    Oracle 10g - Data Pump: Export / Import of Sequences ?

    FireFighter
      Hello,

      I'm new to this forum and also to Oracle (Version 10g). Since I could not find an answer to my question, I open this post in hoping to get some help from the experienced users.

      My question concerns the Data Pump Utility and what happens to sequences which were defined in the source database:
      I have exported a schema with the following command:

      "expdp <user>/<pass> DIRECTORY=DATA_PUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log"

      This worked fine and also the import seemed to work fine with the command:
      "impdp <user>/<pass> DIRECTORY=DATA_PUMP_DIR DUMPFILE=dumpfile.dmp"

      It loaded the exported objects directly into the schema of the target database.

      BUT:
      Something has happened to my sequences. :-(

      When I want to use them, all sequences start again with value "1". Since I have already included data with higher values in my tables, I get into trouble with the PK of these tables because I used sequences sometimes as primary key.

      My question go in direction to:
      1. Did I something wrong with Data Pump Utility?
      2. How is the correct way to export and import sequences that they keep their actual values?
      3. When the behaviour described here is correct, how can I correct the values that start again from the last value that was used in the source database?

      Thanks a lot in advance for any help concerning this topic!

      Best regards
      FireFighter

      P.S.
      It might be that my english sounds not perfect since it is not my native language. Sorry for that! ;-)
      But I hope that someone can understand nevertheless. ;-)
        • 1. Re: Oracle 10g - Data Pump: Export / Import of Sequences ?
          277993
          My question go in direction to:
          1. Did I something wrong with Data Pump Utility?
          I do not think so. But may be with the existing schema :-(
          2. How is the correct way to export and import
          sequences that they keep their actual values?
          If the Sequences exist in the target before the import, oracle does not drop and recreate it. So you need to ensure that the sequences do not already exist in the target or the existing ones are dropped before the import.
          3. When the behaviour described here is correct, how
          can I correct the values that start again from the
          last value that was used in the source database?
          You can either refresh with the import after the above correction or drop and manually recreate the sequences to START WITH the NEXT VALUE of the source sequences.

          The easier way is to generate a script from the source if you know how to do it