Reset Sequence code

From: JOE TESTA <JTESTA_at_longaberger.com>
Date: Fri, 05 Jul 2002 05:48:29 -0800
Message-ID: <F001.004902BE.20020705054829@fatcity.com>

NO, you dont need to DROP the sequence, here is demo code i used to reset a sequence(this resets it back to 1).

SQL> drop sequence test_seq;

Sequence dropped.

SQL> create sequence test_seq;

Sequence created.

SQL>
SQL> declare
  2 seq_hold_var number;
  3 begin
  4 for i in 1 .. 100000 loop
  5 select test_seq.nextval into seq_hold_var from dual;   6 end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.nextval from dual;

   NEXTVAL                                                                      
----------                                                                      
    100001                                                                      

SQL>
SQL> alter sequence test_seq increment by -100000;

Sequence altered.

SQL>
SQL> select test_seq.nextval from dual;

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

SQL>
SQL> alter sequence test_seq increment by 1;

Sequence altered.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: JOE TESTA
  INET: JTESTA_at_longaberger.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jul 05 2002 - 08:48:29 CDT