Author Topic: How to Reset Sequence in Oracle (Single Sequence)  (Read 536 times)

Prakash

  • Newbie
  • *
  • Posts: 17
    • View Profile
How to Reset Sequence in Oracle (Single Sequence)
« on: August 12, 2014, 01:08:47 PM »
-- Get the max value of the column where the sequence is being used, max_value
SELECT MAX (column_name) + 1
  FROM table_name;

-- Get the current value of the sequence, l_value
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the current value of the sequence
ALTER SEQUENCE sequence_name INCREMENT BY -l_value MINVALUE 0;

-- To make the above DDL effective, execute the below command and make the sequence value to 0
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the desired value
ALTER    SEQUENCE sequence_name INCREMENT BY max_value;

-- To make the above DDL effective, execute the below command and make the sequence value to max value of actual column
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to increment by 1 for regular use of the sequence
ALTER  SEQUENCE sequence_name INCREMENT BY 1;

-- To make the above DDL effective, execute the below command and test the sequence that is altered
SELECT sequence_name.NEXTVAL
  FROM DUAL;