OraFAQ Forum: SQL & PL/SQL » How to restart sequence

Home » SQL & PL/SQL » SQL & PL/SQL » How to restart sequence

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to restart sequence [message #11006]

Mon, 01 March 2004 00:13

kausik mitra
Messages: 7
Registered: January 2004

Junior Member

How to restart sequnece values without� droping and recreating it ?� It will start generating value� from the� inital .

Report message to a moderator

Re: How to restart sequence [message #11015 is a reply to message #11006]

Mon, 01 March 2004 03:11

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

You could increment the sequence by the negative amount of the last sequence number. You could also create a procedure to do the same for any sequence. The following example assumes that your sequence starts with 1 and increments by 1.

Code: [Select all] [Show/ hide]

scott@ORA92> -- create and use sequence:
scott@ORA92> CREATE SEQUENCE test_seq
  2  /

Sequence created.

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

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

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         2

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         3

scott@ORA92> -- reset sequence:
scott@ORA92> ALTER SEQUENCE test_seq INCREMENT BY -3 MINVALUE 0
  2  /

Sequence altered.

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

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

scott@ORA92> ALTER SEQUENCE test_seq INCREMENT BY 1 MINVALUE 0
  2  /

Sequence altered.

scott@ORA92> -- resume using reset sequence:
scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

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

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         2

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         3

scott@ORA92> -- create procedure to reset sequence:
scott@ORA92> CREATE OR REPLACE PROCEDURE reset_seq_to_1
  2    (p_seq_name IN VARCHAR2)
  3  AS
  4    v_seq	      NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE
  7  	 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO v_seq;
  8    EXECUTE IMMEDIATE
  9  	 'ALTER SEQUENCE ' || p_seq_name
 10  	 || ' INCREMENT BY -' || v_seq || ' MINVALUE 0';
 11    EXECUTE IMMEDIATE
 12  	 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO v_seq;
 13    EXECUTE IMMEDIATE
 14  	 'ALTER SEQUENCE ' || p_seq_name
 15  	 || ' INCREMENT BY 1 MINVALUE 0';
 16  END reset_seq_to_1;
 17  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> -- reset sequence:
scott@ORA92> EXECUTE reset_seq_to_1 ('test_seq')

PL/SQL procedure successfully completed.

scott@ORA92> -- resume using reset sequence:
scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

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

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         2

scott@ORA92> SELECT test_seq.NEXTVAL FROM DUAL
  2  /

   NEXTVAL
----------
         3

Report message to a moderator

Re: How to restart sequence [message #11016 is a reply to message #11015]

Mon, 01 March 2004 03:16

kausik mitra
Messages: 7
Registered: January 2004

Junior Member

Thanks Barbara.

Report message to a moderator

Re: How to restart sequence [message #11364 is a reply to message #11015]

Mon, 22 March 2004 23:09

Kiema
Messages: 1
Registered: March 2004

Junior Member

Try this Function

CREATE OR REPLACE function restart_seq(SeqName IN varchar2) return Integer
as
PRAGMA AUTONOMOUS_TRANSACTION;
vs integer;
inc integer;
junk integer;
sqName varchar2(50);
begin
sqName := seqName||'.nextval';
execute immediate 'select '||sqName||' from dual' into vs;
inc := 0 - vs;
execute immediate 'alter sequence '||SeqName||' minvalue '|| inc;
execute immediate 'alter sequence '||SeqName||' increment by '||inc;
execute immediate 'select '||sqName||' from dual' into junk;
execute immediate 'alter sequence '||SeqName||' increment by 1';
RETURN junk;
end;

/

Report message to a moderator

Previous Topic:	Table name which has the maximum columns
Next Topic:	BLOBS --- Urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 23:09:10 CDT 2025