If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Sequences out of sync

Hello everyone, is there a scriptable way of figuring out if the database sequences are out of sync?

We have almost 200 sequences and over 900 databases. Every once in a blue moon we have an application problem that takes us a few hours of trouble shooting to figure out that a sequence is out of sync. I fix the offending sequence but it always makes me wonder about the other 199 and we don't have time to check every one of them.

I have googled this a lot of times and have always came up empty handed which makes me think the answer is no but I wanted to ask just to be 100% sure.

You should define what you mean to have a "database sequences are out of sync". If I have a table that has a sequence as a primary key I usually use a trigger to get the next sequence and set the primary key to that value. In that case the value get set to a unique value. It is not guaranteed to stay sorted in index order, but that should not matter. If you want to sort by the order in which records were inserted, then you should use a date field, as in last_updated, created_date, etc. If your sequences use the cache feature, then you can be almost certain that not every sequence generated will not be used.

In our case, we have one of our production application schema with some 110 sequences and 200+ tables.
Due to migrating data from a legacy system, at times the sequences were "out of sync", wich would cause "unique constraint violated".

There is no Oracle view that directly can tie a sequence with the corresponding table where it is used. Therefore, our solution was to create such a table and code a procedure which based on this table would detect which sequences were "out of sync".

We were fortunate that 90% of the sequences were named {table-name}_SEQ or
{short-table-name}_SEQ.

Good luck with your project!

"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Gandolf, I was referting to the next sequence being lower then the actual primary key causing a unique constraint violation. Ya, I wish they were all in one datacenter. That would make life a lot easier.