Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prashant.

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

Answered by: Chris Saxon - Last updated: February 14, 2020 - 11:29 am UTC

Category: SQL Developer - 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 we 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.

and you rated our response

  (2 ratings)

Reviews

February 12, 2020 - 8:47 am UTC

Reviewer: Prashant from India

Thank you, it is helpful. :)

Is there any way to sent it over email, like auto-email notifications by using UTL_MAIL?
Chris Saxon

Followup  

February 12, 2020 - 10:45 am UTC

There are various examples on this site, e.g. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536635800346534388

Job gets automatically dropped on FAIL

February 14, 2020 - 11:16 am UTC

Reviewer: A reader

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

Followup  

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.