reset sequence value to 1

Question

I have test.csv,test1.csv,test2.csv...etc files. These files i am loading into custom table test through sql loader. in table test i have columns named seq_no,process_id apart from columns coimng from flat file mentioned above. i had created two sequence as mentioned below for the above mnetioned two columns.

CREATE SEQUENCE  test  
MINVALUE 1  
INCREMENT BY 1   
START WITH 1  
NOCACHE  
NOORDER   
NOCYCLE ;

and

CREATE SEQUENCE  test1   
MINVALUE 1  
INCREMENT BY 1  
START WITH 1  
NOCACHE         
NOORDER   
NOCYCLE ;

Now, seq_no will get populated depends on the no.of files getting loaded E.G: if v have 5 files then seq_no would be 1,2,3,4,5 And process_id would be inside file how many "FDETL" records are ther that many E.G if v have file 1, FDETL=2 then seq_no=1,process_id=1,2 if v have file 2,FDETL=3 then seq_no=2,process_id=1,2,3. It is some thing like process_id should repeat/file. Now i am using test1.nextval So how can i reset the sequence test1 so that for every file process_id will repeat from 1 to count(FDETL) records?

That is not the purpose of sequences, but you can alter it to reset. — OldProgrammer, Mar 10 '15 at 12:08
If this is the only place you're using that sequence, you can simply drop it and recreate it. — David Faber, Mar 10 '15 at 12:12
Alter the sequence and do a negative increment by to reset it. However, you need to make sure no other users are using the sequence at that time. See my answer. — Lalit Kumar B, Mar 10 '15 at 12:28

Lalit Kumar B · Accepted Answer · 2015-03-10T12:31:42.603

There is no direct way to do it. You need to code it or do it manually such that the sequence is rest to start from 1 every time a particular process ends. There was a similar question on AskTom. You could do it in following steps:

Find out the current value of the sequence.
Alter the sequence to increment by a negative value as returned by the current value of sequence in step 1.
Now the sequence is reset to 0.
Alter the sequence to increment by 1.
Now the sequence is set to 1.

NOTE : The below example is only applicable if this is the only process using the sequence. Else, other users will get ORA-08004: sequence S.NEXTVAL goes below MINVALUE and cannot be instantiated error until you set the sequence increment back to 1.

For example,

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from user_objects;

   NEXTVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12

12 rows selected.

SQL> select s.nextval from dual;

   NEXTVAL
----------
        13

Let's increment it by -13,

SQL> alter sequence s increment by -13 minvalue 0;

Sequence altered.

SQL> select s.nextval from dual;

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

SQL> alter sequence s increment by 1;

Sequence altered.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL>

The sequence is now reset to 1.

reset sequence value to 1

1 Answers1