problem with sequences
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: problem with sequences

  1. #1
    Join Date
    Feb 2001
    Posts
    128
    Hi,

    I seem to run into the sequences problem quite often, I do a full export of the database(prod, 7.3.4) and then
    import(fromuser touser)on the test database(8.1.7.1).
    Everything completes succefully without any warnings, but my sequences are out of order and I end up dropping and recreating them every time. I have a lot of sequences and I don't want to do this everytime.
    Am I doing something wrong during the import.

    I drop all the objects related to the schema and then I do the import(fromuser touser) with ignore=y.

    I would apprciate your input, thanks
    vj

  2. #2
    Join Date
    Apr 2001
    Posts
    219
    Are you caching a part of your sequences? If so, this might be your problem.
    ______________________
    Applications come and go,
    but the data remains!

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Red face


    normally, i just use a script that checks the nextval and then re-creates all the sequences. once you have the script going, then it's not such a pain in the arse.

    - Magnus

  4. #4
    Join Date
    Feb 2001
    Posts
    128
    Yes, I'm using cache 20. so, do I have to drop and recreate it everytime?

    Magnus, can you share that script? if so, please email to nugpot77084@yahoo.com
    Thanks


  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Turn off that caching, that would do the trick. If you want to start from 1 then you would have to recreate your sequence.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking unix script


    the script drops whatever you are creating beforehand so you won't attempt to create a sequence that already exists.
    it's a unix script, but you can just change some of the parameters that get passed like TARGET (schema you are moving to) and SOURCE (schema you are copying):

    spool /tmp/sequences.sql
    select 'drop sequence ${TARGET}.'|| sequence_name ||';'||'
    create sequence ${TARGET}.'|| sequence_name ||'
    increment by 1 start with '|| (last_number + 1) ||
    ' minvalue '|| last_number ||' order;'
    from dba_sequences
    where sequence_owner = upper('${SOURCE}');
    spool off


    - magnus

  7. #7
    Join Date
    Feb 2001
    Posts
    128
    Thank you guys!!

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