Sequence in Oracle

DROP SEQUENCE SCOTT.EMP_SEQ;

CREATE SEQUENCE SCOTT.EMP_SEQ
  START WITH 1
  MAXVALUE 99999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;


-- To know the sequence associated with 'EMP'

SELECT x.sequence_owner, x.SEQUENCE_NAME, B.TABLE_NAME
  FROM all_sequences x, all_tables B
 WHERE x.sequence_owner = B.owner AND B.TABLE_NAME = 'EMP';

--Example-1

SQL> CREATE TABLE my_numbers (my_number NUMBER NOT NULL PRIMARY KEY);

Table created.

SQL>
SQL> INSERT INTO my_numbers
  2     (SELECT ROWNUM FROM user_objects);

93 rows created.

SQL>
SQL> SELECT MAX (my_number) FROM my_numbers;

MAX(MY_NUMBER)
--------------
            93

SQL> CREATE SEQUENCE my_number_sn START WITH 93;

Sequence created.

SQL>
SQL> select my_number_sn.CURRVAL from dual;
select my_number_sn.CURRVAL from dual
       *
ERROR at line 1:
ORA-08002: sequence MY_NUMBER_SN.CURRVAL is not yet defined in this session


SQL> INSERT INTO my_numbers (my_number)
  2       VALUES (my_number_sn.NEXTVAL);
INSERT INTO my_numbers (my_number)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C006831) violated


SQL> DROP SEQUENCE my_number_sn;

Sequence dropped.

SQL>
SQL> CREATE SEQUENCE my_number_sn START WITH 94;

Sequence created.

SQL>
SQL> INSERT INTO my_numbers (my_number)
  2       VALUES (my_number_sn.NEXTVAL);

1 row created.


--Creating sequence on based on maxvalue of a table id

DECLARE
   l_new_seq   INTEGER;
BEGIN
   SELECT MAX (id) + 1 INTO l_new_seq FROM CERT_APPL_USERS;

   EXECUTE IMMEDIATE
         'Create sequence CERT_APPL_USERS_SEQ
                       start with '
      || l_new_seq
      || ' INCREMENT BY 1 MINVALUE 1
                                    MAXVALUE 99999999
                                    NOCYCLE
                                    CACHE 20
                                    NOORDER';
END;
/


-- How to Reset a Sequence in Oracle

create sequence reset_seq start with 1 increment by 1 minvalue 1 maxvalue 999 cache 20 nocycle;

wrong: alter sequence reset_seq restart with 0;

create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin
-- Select the next value of the sequence

    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
   
    dbms_output.put_line('l_value1='||l_value);

-- Set a negative increment for the sequence,
-- with value = the current value of the sequence

    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by -' || l_value || ' minvalue 0';
   
    dbms_output.put_line('l_value2='||l_value);

-- Select once from the sequence, to
-- take its current value back to 0

    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
   
    dbms_output.put_line('l_value3='||l_value);

-- Set the increment back to 1

    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by 1 minvalue 0';
   
    dbms_output.put_line('l_value4='||l_value);
end;
/

 select reset_seq.currval from dual;

 select reset_seq.nextval from dual;

begin
reset_sequence('reset_seq');
end;
/

--------------- OR -----------------

SQL> /

   NEXTVAL
----------
         5

SQL> alter sequence reset_seq increment by -5 minvalue 0;

Sequence altered.

SQL>  select reset_seq.nextval from dual;

   NEXTVAL
----------
         0

SQL> alter sequence reset_seq increment by 1 minvalue 0;

Sequence altered.

Why go through this rigmarole? Why not simply drop and recreate the sequence?

You could drop and recreate the sequence, but the disadvantage is this would invalidate all dependent objects (triggers/stored procedures etc.). Far better to alter and reset the sequence.


Caution!

Reset your sequence when others are not using it simultaneously, else they (or you) may get this error:

ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated
 Link: http://www.oratable.com/reset-sequence-in-oracle/