Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on the destination table after a load.

Is the following sequence of events a safe and acceptable way of handling this?

  1. Backup sequence properties to a table

  2. Drop sequences

  3. Load tables (with metadata)

  4. Find which has the greatest last cached value, backup sequence table or new imported sequence.

  5. Update accordingly

Are there any pitfalls with this method, and if so are there tried and tested methods?

share|improve this question

For sequences the best way is to copy the current value from dba|all|user_sequences.last_number. Fetching the current value from the sequence is not safe; it can have higher values in other instances of the database. Also make sure that your app is not using sequences for ordering ....

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.