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?
This MAY work: analyze the table with compute option.
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
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
I specified NOCACHE in the create sequence statement. It worked.
Thanks Rajeev and to all who replied.