Best practices for ensuring up to date sequences after table import

Question

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?

Answer 1

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