How to Set Current Value of a Sequence Without Dropping/Recreating

Oracle does not let you change the value of a sequence. If you need to change its value, you should re-create the sequence. On the other hand, we can use a trick to change the value of a sequence without recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

Just for fun, let’s create a procedure for it:

Please share
  • 2
  •  
  •  
  •  
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

35 Comments

  1. Jose Escobar

    Nice code! very useful, we just need to double check newvalue – ln 0 or else I am getting an error that INCREMENT BY should be !=0

  2. Pingback: Como colocar um novo valor para sequence no Oracle | ...Um Minuto com Menal - Um espa├žo para voc├¬ estudar, refletir e discutir...

  3. Scott

    This  was a very creative way for us to accomplish the re-sequencing  without having to go to our DBA.  Thanks!

  4. Thomas

    True! Try wrapping lines 13 to 18 like so:

    IF newvalue != ln THEN
    EXECUTE ...
    EXECUTE ...
    EXECUTE ...
    END IF;

  5. VLADIMIR

    DECLARE
    VMAXIMO_SECUENCIAL NUMBER:=0;
    CURSOR CURSOR_SECUENCIALES IS
    SELECT object_name from all_objects
    WHERE object_type=’SEQUENCE’ AND OWNER =’SITC’ AND object_name NOT IN(‘SEQ_IDAUDITORIA’) ORDER BY object_name;
    BEGIN

    FOR CUR IN CURSOR_SECUENCIALES LOOP
    BEGIN
    SELECT LAST_NUMBER INTO VMAXIMO_SECUENCIAL
    –,SEQUENCE_OWNER,sequence_name
    FROM DBA_sequences
    WHERE SEQUENCE_NAME = CUR.object_name ;
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(CUR.object_name);
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(CUR.object_name);
    END;

    –INSERTAMOS LOS SECUENCIALES EN LA TABLA TEMPORAL
    IF VMAXIMO_SECUENCIAL >0 THEN
    DBMS_OUTPUT.PUT_LINE(CUR.object_name||’=>’||TO_CHAR(VMAXIMO_SECUENCIAL));
    END IF;
    END LOOP;

    END;

    • sandhy

      Hi,

      I want to assign the sequence number as the next value of the existing number of the sequence and after inserting the data, I want to delete the inserted rows.
      please let me know how to go about it

      Thanks
      Sa

  6. Steven

    added option to set it to a lower value and test if same as current value.

    CREATE OR REPLACE PROCEDURE SYS.SEQUENCE_NEWVALUE(
    seqowner VARCHAR2,
    seqname VARCHAR2,
    newvalue NUMBER) AS
    ln NUMBER;
    ib NUMBER;
    BEGIN
    SELECT last_number, increment_by
    INTO ln, ib
    FROM dba_sequences
    WHERE sequence_owner = upper(seqowner)
    AND sequence_name = upper(seqname);

    IF newvalue > ln THEN
    EXECUTE IMMEDIATE ‘ALTER SEQUENCE ‘ || seqowner || ‘.’ || seqname ||
    ‘ INCREMENT BY ‘ || (newvalue – ln);
    ELSIF newvalue < ln THEN
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname ||
    ' INCREMENT BY ' || -(ln – newvalue);
    ELSE
    RETURN;
    END IF;

    EXECUTE IMMEDIATE 'SELECT ' || seqowner || '.' || seqname ||
    '.NEXTVAL FROM DUAL' INTO ln;

    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname
    || ' INCREMENT BY ' || ib;
    END;
    /

  7. ramgbile

    Your solution need privilege when using system object. Other idea is quite good:

     

    PROCEDURE jc_reset_sequence (
    seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS

    cval INTEGER;
    inc_by VARCHAR2(25);

    BEGIN
    EXECUTE IMMEDIATE ‘ALTER SEQUENCE ‘ ||seq_name||’ MINVALUE 0′;

    EXECUTE IMMEDIATE ‘SELECT ‘ ||seq_name ||’.NEXTVAL FROM DUAL’
    INTO cval;

    cval := cval – startvalue + 1;
    IF cval < 0 THEN
    inc_by := ‘ INCREMENT BY ‘;
    cval:= ABS(cval);
    ELSE
    inc_by := ‘ INCREMENT BY -‘;
    END IF;

    EXECUTE IMMEDIATE ‘ALTER SEQUENCE ‘ || seq_name || inc_by ||
    cval;

    EXECUTE IMMEDIATE ‘SELECT ‘ ||seq_name ||’.NEXTVAL FROM DUAL’
    INTO cval;

    EXECUTE IMMEDIATE ‘ALTER SEQUENCE ‘ || seq_name ||
    ‘ INCREMENT BY 1’;

    END jc_reset_sequence;

  8. Shailesh Manek

    Nice script. Very useful. Used today for set of sequences change request.

  9. SmittyInPhilly

    Very nice work around, should save me a lot of time and anquish in my new project.  Thank you for sharing!

    SmittyInPhilly

  10. Bruno Freitas

    I make an alternative that the user don’t need to know de values, the system get and use variables to update.

     

    • Bruno Freitas

      I missed:

      –aqui ele puxa o pr├│ximo valor usando o incremento necess├írio
      select sq_cd_tp_taca_serv.nextval

      –aqui volta o incremento para 1, para que futuras inser├ž├Áes funcionem normalmente
      alter sequence sq_cd_tp_taxa_serv increment by 1

Leave a Reply to Douglas Carvalho Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.