Sequences out of sync
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sequences out of sync

Hybrid View

  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Question 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!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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...
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460
    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

  4. #4
    Join Date
    Apr 2013
    Posts
    2
    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!

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool

    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
  •  



Click Here to Expand Forum to Full Width