Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prashant.

Asked: February 11, 2020 - 2:19 pm UTC

Last updated: February 14, 2020 - 11:29 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

I have few sequences which are about to expires and I need notification over emails when last_number increases. Can you suggest how to do it.

and Chris said...

Expire? Sequences don't expire!

They can however get near their maxvalue. The upper limit for this is 28 digits long. That's 28 octillion. If you've managed to use all those up... I hate to think how big your data is!

Or - more likely - someone picked a lower maxvalue for ... reasons ...

In which case you're best off increasing the maxvalue. You can find sequences close to the limit by querying *_sequences and comparing maxvalue to last_number:

create sequence s
  maxvalue 500;

select sequence_name, max_value, last_number
from   user_sequences
where  ( max_value - last_number ) < 1000; -- or whatever threshold you deem safe

SEQUENCE_NAME   MAX_VALUE   LAST_NUMBER   
S                         500              1 

alter sequence s
  maxvalue 999999999999999999;
  
select sequence_name, max_value, last_number
from   user_sequences
where  ( max_value - last_number ) < 1000;

no rows selected


You can use the query above to drive the data for an email.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Prashant, February 12, 2020 - 8:47 am UTC

Thank you, it is helpful. :)

Is there any way to sent it over email, like auto-email notifications by using UTL_MAIL?
Chris Saxon
February 12, 2020 - 10:45 am UTC

Job gets automatically dropped on FAIL

A reader, February 14, 2020 - 11:16 am UTC

Hi Chris,
Though job is getting auto drop when it gets "SUCCEEDED".
However its getting dropped even if it gets "FAILED" in USER_SCHEDULER_JOB_RUN_DETAILS and there is no option to rerun it.
Could you please help to tackle FAILED job scenario.
Chris Saxon
February 14, 2020 - 11:29 am UTC

You've lost me - what's this got to do with the original question?

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.