Getting min value of the sequence

Question

I have around 100 sequences defined in the database, that are set to some number after the testing has been performed.

For example: There is a sequence: suppier_seq. That would start with 1 and now its currval is 101.

I need to reset all the sequences that exist based on their min value.

If I extract the DDL of the sequence, it comes with the present value as start with:

CREATE SEQUENCE  "RMS14"."SUPPLIER_SEQUENCE"  MINVALUE 0 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 101 CACHE 100 NOORDER  NOCYCLE ;

I need to restart the sequence with the minimum value.

How can i capture the minimum value of the sequence via SQL?

With the MINVALUE it was originally created with, or the START WITH? The *original* START WITH isn't stored anyway, so are you assuming you want to start with MINVALUE + 1? Can't you drop and recreate them from your controlled DDL scripts - you have those in source control, right? — Alex Poole, Jun 16 '16 at 11:54
Have a read of [this](http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle) — StevieG, Jun 16 '16 at 12:18

score 4 · Accepted Answer · edited May 23 '17 at 12:22

You can't tell what the sequence's original START WITH value was. You can only base it on the MINVALUE, either using that (even if it's zero), or adding one to it, or only doing that if it's non-zero.

You can get the current values for your own sequences from the user_sequences view, and use a PL/SQL block to loop over them, generating drop and create statements on the way:

set serveroutput on
begin
  for r in (
    select 'DROP SEQUENCE "' || sequence_name || '"' as drop_stmt,
      'CREATE SEQUENCE "' || sequence_name || '"'
        || ' MINVALUE ' || min_value
        || ' MAXVALUE ' || max_value
        || ' INCREMENT BY ' || increment_by
        || ' START WITH ' || min_value
        || case when cache_size = 0 then ' NOCACHE' else ' CACHE ' || cache_size end
        || case when order_flag = 'Y' then ' ORDER' else ' NOORDER' end
        || case when cycle_flag = 'Y' then ' CYCLE' else ' NOCYCLE' end
        as create_stmt
    from user_sequences
  )
  loop
    dbms_output.put_line(r.drop_stmt);
--    execute immediate r.drop_stmt;
    dbms_output.put_line(r.create_stmt);
--    execute immediate r.create_stmt;
  end loop;
end;
/

I've left the execute immediate statement commented out to try to avoid accidents from this being copied and pasted without any checks; initially it will just display the commands it would run, e.g.

PL/SQL procedure successfully completed.

DROP SEQUENCE "SUPPLIER_SEQUENCE"
CREATE SEQUENCE "SUPPLIER_SEQUENCE" MINVALUE 0 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 0 CACHE 100 NOORDER NOCYCLE
...

If you're working in another schema and have the right permissions you can query all_sequences or dba_sequences instead, and specify the owner:

    select 'DROP SEQUENCE "' || sequence_owner || '"."' || sequence_name ||'"' as drop_stmt,
      'CREATE SEQUENCE "' || sequence_owner || '"."' || sequence_name || '"'
        || ' MINVALUE ' || min_value
...

Another approach is to alter the sequence to set the INCREMENT BY to minus the current highest value (or the highest value minus the MINVALUE), call nextval, and then reset the increment. That's a bit messy dynamically, not least because you need a second level on dynamic statement to get the current sequence value when it's done directly in a loop like this, but basically the same idea:

declare
  l_nextval number;
begin
  for r in (
    select 'BEGIN EXECUTE IMMEDIATE ''ALTER SEQUENCE "' || sequence_name || '"'
        || ' INCREMENT BY -''|| ("' || sequence_name || '".nextval - ' || min_value ||'); END;'
        as alter_stmt_1,
      'SELECT "' || sequence_name || '".nextval from dual' as adjust_stmt,
      'ALTER SEQUENCE "' || sequence_name || '"'
        || ' INCREMENT BY ' || increment_by
        as alter_stmt_2
    from user_sequences
  )
  loop
    dbms_output.put_line(r.alter_stmt_1);
--    execute immediate r.alter_stmt_1;
    dbms_output.put_line(r.adjust_stmt);
--    execute immediate r.adjust_stmt into l_nextval;
    dbms_output.put_line(r.alter_stmt_2);
--    execute immediate r.alter_stmt_2;
  end loop;
end;
/

PL/SQL procedure successfully completed.

BEGIN EXECUTE IMMEDIATE 'ALTER SEQUENCE "SUPPLIER_SEQUENCE" INCREMENT BY -'|| ("SUPPLIER_SEQUENCE".nextval - 0); END;
SELECT "SUPPLIER_SEQUENCE".nextval from dual
ALTER SEQUENCE "SUPPLIER_SEQUENCE" INCREMENT BY 1

You could also generate the dynamic statements inside the loop, which is perhaps a bit neater for this approach:

declare
  l_alter_stmt_1 varchar2(4000);
  l_alter_stmt_2 varchar2(4000);
  l_adjust_stmt varchar2(4000);
  l_nextval number;
begin
  for r in (select * from user_sequences) loop
    l_adjust_stmt := 'select "' || r.sequence_name || '".nextval from dual';
    execute immediate l_adjust_stmt into l_nextval;
    l_alter_stmt_1 := 'ALTER SEQUENCE "' || r.sequence_name || '"'
        || ' INCREMENT BY '|| (r.min_value - l_nextval);
    l_alter_stmt_2 := 'ALTER SEQUENCE "' || r.sequence_name || '"'
        || ' INCREMENT BY ' || r.increment_by;
    dbms_output.put_line(l_alter_stmt_1);
--    execute immediate r.alter_stmt_1;
    dbms_output.put_line(l_adjust_stmt);
--    execute immediate l_adjust_stmt into l_nextval;
    dbms_output.put_line(l_alter_stmt_2);
--    execute immediate l_alter_stmt_2;
  end loop;
end;
/

PL/SQL procedure successfully completed.

ALTER SEQUENCE "SUPPLIER_SEQUENCE" INCREMENT BY -101
select "SUPPLIER_SEQUENCE".nextval from dual
ALTER SEQUENCE "SUPPLIER_SEQUENCE" INCREMENT BY 1

The question is specifically about 11g, but this is even simpler in 12c.

Getting min value of the sequence

1 Answers1