-
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.
Thank you for your help in advanced!
-
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.
900 databases is a lot. You must have a huge data center...
-
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.
LKBrwn, Thank you as this is what I was looking for!
-
Our table is simple:
Code:
CREATE TABLE MYSCHEMA_SEQUENCES
(
TABLE_NAME VARCHAR2(40) NOT NULL,
SEQUENCE_NAME VARCHAR2(40) NOT NULL,
SEQ_COLUMN VARCHAR2(40)
INCREMENT_BY NUMBER,
LAST_NUMBER NUMBER,
TABLE_MAX_NUMBER NUMBER
);
Then our procedure will first populate the LAST_NUMBER and then build a dynamic sql to select the MAX(SEQ_COLUMN ) from TABLE_NAME.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|