 |
| |
|
Oracle > Sequences articles: • Resetting a sequence to a desired value without dropping the sequence
|
Return to index of articles
Resetting a sequence to a desired value without dropping the sequence
Category: Oracle
Category: Sequences
Step 1 : Find out the INCREMENT BY parameter for the sequence with the following query:
Select INCREMENT_BY
From USER_SEQUENCES
Where SEQUENCE_NAME = 'EMP_NO_SEQ' ;
This will return 1. It is not always necessary to run the above query to find out the INCREMENT BY value. You can find it out by looking at the column data for which the sequence is used.
Step 2 : Alter the sequence with a negative INCREMENT BY value:
alter sequence EMP_NO_SEQ increment by -1;
Step 3 : Run the PL/SQL block below to reset the sequence value to a desired number:
DECLARE
TEMP Number(10);
BEGIN
While(TRUE)
LOOP
Select EMP_NO_SEQ.nextval
Into TEMP
From DUAL;
IF ( TEMP = 100) THEN
Exit;
END IF;
END LOOP;
EXCEPTION
When Others Then
Dbms_output.Put_line(SQLERRM);
END;
Step 4 : Again reset the INCREMENT BY value to original
alter sequence EMP_NO_SEQ increment by 1;
9/15/2003
Source: Visit
|
|
|
|
 |
|
| |
All contents ©2003 Ten Volt Consulting.
All rights reserved.
Unauthorized duplication or use is a violation of applicable laws.
Webmaster Contact |
