Reset sequence to a specific value

Question

We're creating a 'blank'/minimal copy of an existing database and want to reset one of the sequences to a value. Putting the number in the below works, but I want to make it reusable when sequence in the export has a higher number, trying to avoid dropping & recreating.

Can you do the equivalent of a subselect and calculation to get the value or does this need to be set as a variable 1st?

alter sequence users.SQ_USER_ID INCREMENT BY  (99999 - select users.SQ_USER_ID.nextval from dual) nocache;
select users.SQ_USER_ID.nextval from dual;
alter sequence users.SQ_USER_ID INCREMENT BY 1  cache 20;

the aim being to end with the sequence at nextval as 99999.

possible duplicate of [Need to reset the value of sequence in Oracle](http://stackoverflow.com/questions/10159840/need-to-reset-the-value-of-sequence-in-oracle) — Ben, Apr 17 '14 at 11:56

score 4 · Accepted Answer · answered Apr 17 '14 at 10:34

You can use a negative increment to reset a sequence to a lower value - this script (it's just a PL/SQL block version of yours) will work with sequence values larger than 9999 without problems):

declare
 currval pls_integer;
 diff pls_integer;
begin
  select SQ_USER_ID.nextval into currval from dual;
  dbms_output.put_line('value before alter: ' || currval);
  diff := 99999 - currval;
  dbms_output.put_line('diff: ' || diff);
  execute immediate ' alter sequence SQ_USER_ID INCREMENT BY ' ||  diff || 'nocache';
  select SQ_USER_ID.nextval into currval from dual;
  dbms_output.put_line('value after alter: ' || currval);
  execute immediate 'alter sequence SQ_USER_ID INCREMENT BY 1  cache 20';
end;

Reset sequence to a specific value

1 Answers1

Related