Posted on by Biju

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…

This was added to monitoring tool immediately… 🙂

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;

 

5 Replies to “Sequences Nearing Limit”

  2. Is there any tool in the market that does this monitoring and reports the results i.e. sequences nearing limit based on custom rules for each sequence?

    In our case, we define sequence max_value as a very large number e.g. 9999999999999999 whereas the table column that stores the sequence value is NUMBER(8).

    We are currently using Oracle 11g.

    1. If you are able to define a custom rule (like a SQL), almost all monitoring tools have custom monitoring provision, where you could add your own monitoring.

      1. Thanks for the reply but my question still remains. We need to evaluate monitoring tools therefore we would like to know if there are any tools in the market.

        Thanks

        1. There are several monitoring tools in the market. Email me and we can discuss specifics. biju (dot) thomas (at) gmail (dot) com.

Comments are closed.