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... :-)