CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-sequence
Paul S
Paul S

Reputation: 113

Oracle Sequences Out of Sync/Not Matching Table

We have an Oracle 11.2 database that has multiple, if not all, sequences out of sync. Their last number doesn't match the max number of their relevant database tables.

I can't seem to find any possible causes online but one possibility that came to mind is - is it possible to restore a database without restoring sequences? My theory is that a Production copy was place over a QA database, and the sequences never updated.

Upvotes: 2

Views: 1824

Answers (1)

wolφi
wolφi

Reputation: 8361

Yes, that is a likely scenario, which I've seen quite often. If the QA database is copied from production backups, then the sequence values are ok.

If, however, the content of the production tables are copied, for instance by expdp/impdb, then you'll have exactly the mismatch you are describing.

Two solutions come to mind:

  1. export/import tables and sequences from production to QA.
  2. run a script on the QA database that resets the sequences according to the max value in the tables

In case hibernate is involved, it might be a totally different story...

Upvotes: 3

Related Questions