Tuesday, January 3, 2012

Sequences Nearing Limit

Happy New Year 2012!

During the holidays we hit a production issue on the EBS instance. It was one of the Sequences reaching the maximum limit, but took a lot of time for our admins and Oracle support to figure out where the issue was... If we had the following SQL as part of the monitoring jobs, could have avoided the issue altogether...


SELECT sequence_owner,
       sequence_name,
       last_number,
       max_value,
       cache_size
FROM   dba_sequences
WHERE  last_number > max_value - CASE
                                   WHEN max_value > 50000 THEN 10000
                                   ELSE 200
                                 END
       AND cycle_flag = 'N'
       AND max_value != -1; 

This was added to monitoring tool immediately... :-)


No comments:

Post a Comment

Thank you for your time... Appreciate your feedback.