1

Possible Duplicate:
How do I reset a sequence in Oracle?

I have a table in oracle db. The table contains huge number of rows always, say a few hundred thousands. I have set a id in that table and used a sequence and a trigger such that every time a record is inserted id is automatically generated and inserted. Now while traversing the existing rows through java code, sometimes the data in that table can be truncated and new rows can be inserted. In that case, I want the sequence to start again from 1 when the table is empty.

The sequence looks like 

CREATE SEQUENCE DUMMY_DETAILS_SEQ
  START WITH 356005
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;
Community
  • 1
  • 1
ntonzz
  • 87
  • 1
  • 2
  • 10
  • 4
    You have to "reset" the sequence (usually done using a negative `increment by`) whenever you truncate the table. If you don't have any dependent objects (eg: triggers), you can drop and recreate the sequence too. You can automate this process using a trigger on truncate. – NullUserException Oct 05 '12 at 16:57

1 Answers1

0

If the table gets empty and you need to reset the sequence, you have to drop it and then, recreate it. Use a procedure receiving the table name, table id field and sequence name to do it.

Also, you can find the current value and use increment by with a negative value so you turn it back to zero. 

alter sequence seq increment by -inc_value minvalue 0;
Alfabravo
  • 7,493
  • 6
  • 46
  • 82