2

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.

Paul S
  • 113
  • 1
  • 1
  • 13
  • Can you show the numbers you're talking about - what the data dictionary tables show for the sequences, what the table data you refer to shows? This may be normal, if last_number is just [higher than you expect but within the cache size of the last used number](https://stackoverflow.com/a/23632947/266304), but it's hard to tell quite what you're seeing. As for the restore, it depends how that as done; a datapump import can certainly 'restore' tables without touching indexes, as they aren't related. – Alex Poole May 30 '18 at 11:46
  • ... without touching *sequences*, not indexes (sigh) – Alex Poole May 30 '18 at 11:54

1 Answers1

3

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

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Thanks. Wasn't sure if excluding sequences was an option. I've asked the person who did the impdb if they excluded it. I'm also looking into a script to point 2. – Paul S May 30 '18 at 13:18
  • 1
    They might be in the export, but not taken during import, because the objects already existed. – wolφi May 30 '18 at 13:24