Q:reset sequnce, help around understanding

Question

I do not understand why there is a select statement in this code and why it has twice the alter sequence statement for increment by

I found this code on the site asktom

CREATE OR REPLACE 
PACKAGE pkg_seq AS
      PROCEDURE alterSequenceLast(
          sequenceName IN VARCHAR2,
          inc in integer);
END;
/

    CREATE OR REPLACE
PACKAGE BODY pkg_asktom AS
    PROCEDURE alterSequenceLast(
        sequenceName IN VARCHAR2,
        inc in integer)
    is
        stmt VARCHAR2(2000);
        l_n  number;
    begin
        BEGIN
            -- alter increment to inc
        stmt := 'ALTER SEQUENCE ' ||  sequenceName ||' INCREMENT BY ' ||inc;
            dbms_output.put_line('Executing ''' || stmt || '''');
            EXECUTE IMMEDIATE stmt;
            -- read the next value
            stmt := 'SELECT ' || sequenceName || '.NEXTVAL FROM DUAL';
            dbms_output.put_line('Executing ''' || stmt || '''');
            EXECUTE IMMEDIATE stmt into l_n;
            -- alter increment to 1
            stmt := 'ALTER SEQUENCE ' ||  sequenceName ||' INCREMENT BY 1';
            dbms_output.put_line('Executing ''' || stmt || '''');
            EXECUTE IMMEDIATE stmt;
        END;
    end alterSequenceLast;
END;
/

After execution I get this

SQL>  exec pkg_seq.alterSequenceLast('acc_seq',1);
Executing 'ALTER SEQUENCE acc_seq INCREMENT BY 1'
Executing 'SELECT acc_seq.NEXTVAL FROM DUAL'
Executing 'ALTER SEQUENCE acc_seq INCREMENT BY 1'

I would do it this way, I would return the sequence to the beginning.

CREATE OR REPLACE 
PACKAGE pkg_seq AS
      PROCEDURE alterSequenceLast(
          sequenceName IN VARCHAR2,
          inc in integer);
END;
/

    CREATE OR REPLACE
PACKAGE BODY pkg_seq AS
    PROCEDURE alterSequenceLast(
        sequenceName IN VARCHAR2,
        inc in integer)
    is
        stmt VARCHAR2(2000);
        l_n  number;
    begin
        BEGIN
stmt := 'ALTER SEQUENCE ' ||  sequenceName ||' restart start with ' ||inc;
         dbms_output.put_line('Executing ''' || stmt || '''');
         EXECUTE IMMEDIATE stmt;
         -- alter increment to 1
         stmt := 'ALTER SEQUENCE ' ||  sequenceName ||' INCREMENT BY 1';
            dbms_output.put_line('Executing ''' || stmt || '''');
            EXECUTE IMMEDIATE stmt;
        END;
    end alterSequenceLast;
END;
/

Thaks advance, Petar.

`restart start with` is an undocumented feature which works in 12c. Read [this](https://stackoverflow.com/a/19673327/7998591) . It says that *" it's an unsupported feature.. It's possible the command will disappear some day"* — Kaushik Nayak, Dec 15 '18 at 14:05
Thanks, change the sequence acc_seq start with 1; this code does not work, what should I change? — Goku, Dec 15 '18 at 14:11
*"This code does not work"*? What doesn't work? Is there an error or an unexpected result? — William Robertson, Dec 16 '18 at 11:57

APC · Accepted Answer · 2018-12-16T13:07:11.563

This code is for setting sequence to start from a new value.

For instance, suppose we have a table which usually has its ID generated by the sequence our_seq but for some reason we have loaded 10000 records without using the sequence. What happens if we insert a new record? As it stands our_seq.nextval will generate an ID which clashes with the key of a bulk-loaded record.

So, we need to adjust the value of the sequence. One way to do that would be to select nextval ten thousand times. Or we could

alter the sequence to increment by 10000
select nextval for the sequence once
alter the sequence to increment by 1 once again

The Tom Kyte package is a way of automating the second approach. Personally I would regard it as an interesting curio. If you need to undertake this operation often enough to need to automate it you probably should re-consider your data loading practices.

could [we] increase the sequence for 10001 without select a statement?

No. The only supported way to do that would be to drop the sequence and re-create it with the required start with value. Dropping an object is more complicated because of grants, code invalidations, etc.

Supposing your interest is you have a sequence of say current value = 10001 and you want to reset it so it starts from 1 again you would pass a negative increment -10001. Be careful you don't decrement it past the minvalue (by default 1); the database won't warn you or hurl until you issue a nextval at which point it will tell you:

ORA-08004: sequence OUR_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

Thanks, also he could increase the sequence for 10001 without select a statement — Goku, Dec 15 '18 at 16:28
No. The only *supported* way to do that would be to drop the sequence and re-create it with the required `start with` value. Dropping an object is more complicated because of grants, code invalidations, etc — APC, Dec 15 '18 at 22:06

Q:reset sequnce, help around understanding

1 Answers1