Monitor Oracle sequence

In Oracle database, sequence is the way to generating unique key for records.
But there is max number limit till you can generate the key. You need to monitor sequence number growth rate. If it reach a threshold you need to do defragmentation in the table.


You can run the below sql from monitoring tool or as a crontab script. It will show utilization percentage of the sequence.

 

SELECT S.SEQUENCE_OWNER,
       S.SEQUENCE_NAME,    
       TO_CHAR( S.LAST_NUMBER,'99999999999999999999')   CURRENT_VALUE, 
      TO_CHAR( (S.LAST_NUMBER +S.INCREMENT_BY),'99999999999999999999')    NEXT_VALUE ,        
       TO_CHAR( S.MAX_VALUE,'99999999999999999999')  MAX_VALUE ,
       TO_CHAR( S.MIN_VALUE,'99999999999999999999')   MIN_VALUE,
       (S.MAX_VALUE - S.LAST_NUMBER) AS AVAILABLE_BAN,
     TRUNC(  (( S.LAST_NUMBER-S.MIN_VALUE)*100)/(S.MAX_VALUE- S.MIN_VALUE),2) 
|| '%' AS UTILIZATION  FROM DBA_SEQUENCES S
WHERE SEQUENCE_NAME IN (''TEST_SEQ1' ,'TEST_SEQ2'  )AND SEQUENCE_OWNER = 'TEST' 
ORDER BY  
TRUNC(  (( S.LAST_NUMBER-S.MIN_VALUE)*100)/(S.MAX_VALUE- S.MIN_VALUE),2) DESC;