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 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