Only import sequences?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Only import sequences?

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Only import sequences?

    Hi,

    I fully exported a db.

    I only import 1 user. But I made a mistake: I dropped the tables, but forgot to drop the sequences.

    Oracle documentation says:
    Sequences
    If sequence numbers need to be reset to the value in an export file as part of an import, you should drop sequences. A sequence that is not dropped before the import is not set to the value captured in the export file, because Import does not drop and re-create a sequence that already exists. If the sequence already exists, the export file's CREATE SEQUENCE statement fails and the sequence is not imported.

    Now, old sequences are used, with wrong values.

    Can I drop the sequences and import them seperately, without importing something else?

    Help is highly appreciated!
    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I'm pretty sure it's going to try and import the tables etc again.

    Why not try dropping the sequences then doing an inport with:

    ROWS=N INGNORE=Y

    This won't try to load the rows, and won't chuck out messages when objects already exist.

    Another alternative is to capture the DDL for the sequences from the original database and load them manually. You can do this directly or do the import using the indexfile option to generate a script. You can then pick out the sequence DDL from this script.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Step 1: Drop the Seq.
    Step 2: Find out the max value from the table.
    Step 3: Recreate the seq again starting with max value obtained in step 2.

    Or you can query from dba_sequences to get the last value(LAST_NUMBER column), and recreate the seq.

    Tamil

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I drop the sequences before the import. As soon as the import starts and before Oracle starts to import tables, Oracle automaticly imports all sequences. Now I only have to import a dummy table to activate this.

    The programmers had a bright idea: lets name the sequences completely random and if we don't document this, its even more brilliant!
    So it's hard to determine which table uses which sequence...
    (don't worry, I took all developers outside and had them shot - just kidding offcourse)

    Relying on column "last_number" is tricky, because the sequence are cached, so the that column does not exactly know the last_number. I heared that's an isssue. In a test I added 1000 at each sequence to be sure and the complete unknowns I started at 1000000. So the startvalue is always higher than the highest field-value.

    Thanks y'all!
    Last edited by efrijters; 09-18-2003 at 03:51 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Jan 2003
    Location
    Delhi
    Posts
    63
    Hi Tim,

    /***do the import using the indexfile option to generate a script. You can then pick out the sequence DDL from this script.**/

    How i will get the sequence info from indexfile= option

    I think we can get only index info.

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You're quite right. It contains tables and indexes but no sequence definitions.

    Sorry
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    If you use the SHOW=Y option it will likely show you what the commands are it would have used to generate the SEQUENCES. The problem is that it is in a not very friendly format that involves some tinkering.

    If you are running under UNIX it is relatively easy to apply a grep command to the dmp file and pull out all the CREATE SEQUENCE commands.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width