In MySQL we can easily use auto increment integer for the primary key. Its a bit different in Oracle, we need to create Sequence for each table containing autonumber and then or create trigger which automatically make the sequence increment. Sometimes we need to reset the number,
To reset a sequence SEQ created through the command:
1. Search for its current value via the command:
SEQ.CURRVAL SELECT FROM DUAL;
2. Then change the sequence by adding the option by adding increment value VAL(current value – 1) to reset the sequence to 1:
ALTER SEQUENCE SEQ INCREMENT by -VAL;
3. Then run the command that will reset the sequence:
SEQ.NEXTVAL SELECT FROM DUAL;
4. To restore the increment of the sequence:
ALTER SEQUENCE SEQ INCREMENT by 1;
Alternatively there is an easy way using procedures learned from Tom Kyte Oracle Guru
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate
‘alter sequence ‘ || p_seq_name || ‘ increment by -‘ || l_val ||
‘ minvalue 0’;
execute immediate
‘select ‘ || p_seq_name || ‘.nextval from dual’ INTO l_val;
execute immediate
‘alter sequence ‘ || p_seq_name || ‘ increment by 1 minvalue 0’;
end;
/
Just run the procedure and put sequence number.. Done! Hopes help you