Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer

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

Your Answer

 
discard

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.