We help IT Professionals succeed at work.

Updating Sequences with a select statement

Coco Beans
Coco Beans used Ask the Experts™
on
I'm doing testing on a data warehouse.  I have to keep resetting the sequences. I'm looking for a quicker way to do it

some like this......

DROP SEQUENCE SEQ_PAY_STG_DAY_CAL;

CREATE SEQUENCE SEQ_PAY_STG_DAY_CAL
  START WITH (SELECT  last_number
  FROM all_sequences
  WHERE sequence_name = 'SEQ_DIM_CALENDAR')'
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;



I have about 20 to do.


Any ideas? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Use ALTER SEQUENCE, you don'd need and don't want to DROP the sequence (because, for example, that would lose explicit permissions granted to that sequence).
Coco BeansDesigner

Author

Commented:
but I still can't update it using a select?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You could generate the needed ALTER commands using a SELECT, but a SELECT does not change data or settings.
Coco BeansDesigner

Author

Commented:
I'm not sure what you mean.  I still can't update the DML with a DDL
Mark GeerlingsDatabase Administrator

Commented:
With an "alter sequence..." command it is still a multi-step process, but that is better than dropping the sequence as Scott Pletcher indicated, because then you lose the grants (if any) on the sequence.

Here are the steps you need to do for each sequence:  
1. SELECT  last_number FROM all_sequences WHERE sequence_name = '&seq_name';
2. alter sequence &seq_name increment by last_number * -1;  -- This assumes you want them set back to 1.
3. select &seq_name.nextval from dual;
4. alter sequence &seq_name increment by 1;

I used a PL\SQL procedure to do this for multiple sequences in the past.  I'll look for that source code and post it here later if I can find it.
Designer
Commented:
I did it manually