Export/Import with Sequences
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Export/Import with Sequences

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    7

    Question

    I did a USER export - user1 has a table which uses a sequence to increment its primary key field. I then imported user1's objects into user2's tablespace. The existing sequence is retained in the import but when I do an insert on the imported table, the next value is not incremented by 1 but by around 20. For example, if the last number in the sequence is 4, I want the next insert to be 5 instead of 21. I've tried including "increment by 1" in the the create sequence statement. What can I do to fix this problem?

    Thanks,
    Mia

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    This MAY work: analyze the table with compute option.

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    by default Oracle CACHEs 20 sequence numbers in memory for better performance.

    I guess if you look at the last_number in the dba_sequences for a particular sequence you may find if off by a value (20 at the most if it was the default)

    Check the CACHE_SIZE of the sequence.
    You may want to Alter the sequence to NOCACHE


    - Rajeev
    Rajeev Suri

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    This is a know issue when you import sequences which were created with cache clause. It's advisable create the sequences with clause nocache due to this problem
    If you have to do this often do as rsuri suggested, alter the sequence with nocache clause

  5. #5
    Join Date
    Jan 2001
    Posts
    7

    Talking NOCACHE worked

    Rajeev,

    I specified NOCACHE in the create sequence statement. It worked.

    Thanks Rajeev and to all who replied.

    Mia

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