Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ALTER SEQUENCE NEXTVAL

RE: ALTER SEQUENCE NEXTVAL

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Thu, 7 Sep 2000 15:00:08 -0400
Message-Id: <10612.116440@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C018FD.D77AA80A
Content-Type: text/plain;

        charset="iso-8859-1"

Yes, that's what I'm going to have to do. I was just hoping there was a shorter way out than writing dynamic sql. Thanks

-----Original Message-----
From: Steve McClure [mailto:steve_at_pactr.com] Sent: Thursday, September 07, 2000 3:31 PM To: Multiple recipients of list ORACLE-L Subject: Re: ALTER SEQUENCE NEXTVAL

Lisa,  

When I have had need to do this I just dropped the sequence and recreated it with the 'start with' value I required.  

Steve McClure

I thought I saw someone refer to doing this yesterday on the list. I swear I thought it was possible to alter a sequence to reset the nextval to a number you choose, ex., set it back to 1. Turns out the documentation doesn't state you can, and it errors out. Am I missing something? I am writing a procedure to drop and recreate the sequence instead.

  1* alter sequence seq_gn_special_offers_id nextval = 1 SQL> /
alter sequence seq_gn_special_offers_id nextval = 1

                                        * 
ERROR at line 1:
ORA-02286: no options specified for ALTER SEQUENCE

SQL> alter sequence seq_gn_special_offers_id set nextval = 1; alter sequence seq_gn_special_offers_id set nextval = 1

                                        * 
ERROR at line 1:
ORA-02286: no options specified for ALTER SEQUENCE

Lisa Rutland Koivu
Oracle Database Administrator (do I dare call myself that??) Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174 
F: 954.484.2933 
C: 954.658.5849 

http://www.qode.com <http://www.qode.com>

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

------_=_NextPart_001_01C018FD.D77AA80A
Content-Type: text/html;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>ALTER SEQUENCE NEXTVAL</TITLE>

<META content=3D"MSHTML 5.00.3103.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D588405918-07092000>Yes,=20
that's what I'm going to have to do.&nbsp; I was just hoping there was = a shorter=20
way out than writing dynamic sql.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D588405918-07092000>Thanks</SPAN></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT = face=3DTahoma=20
  size=3D2>-----Original Message-----<BR><B>From:</B> Steve McClure=20   [mailto:steve_at_pactr.com]<BR><B>Sent:</B> Thursday, September 07, 2000 = 3:31=20
  PM<BR><B>To:</B> Multiple recipients of list = ORACLE-L<BR><B>Subject:</B> Re:=20
  ALTER SEQUENCE NEXTVAL<BR><BR></DIV></FONT>

  <DIV><FONT face=3DArial size=3D2>Lisa,</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>When I have had need to do this I =
just dropped=20
  the sequence and recreated it with the 'start with' value I=20   required.</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>Steve McClure</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr=20

  style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; = MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

    <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>     <DIV=20
    style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20

    <A href=3D"mailto:lkoivu_at_qode.com" title=3Dlkoivu_at_qode.com>Koivu, = Lisa</A>=20
</DIV>

    <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20     href=3D"mailto:ORACLE-L_at_fatcity.com" = title=3DORACLE-L_at_fatcity.com>Multiple=20

    recipients of list ORACLE-L</A> </DIV>     <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, September = 07, 2000 6:30=20

    AM</DIV>
    <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> ALTER SEQUENCE = NEXTVAL</DIV>

    <DIV><BR></DIV>
    <P><FONT face=3DArial size=3D2>I thought I saw someone refer to = doing this=20

    yesterday on the list.&nbsp; I swear I thought it was possible to = alter a=20

    sequence to reset the nextval to a number you choose, ex., set it = back to 1.=20

    Turns out the documentation doesn't state you can, and it errors = out.&nbsp;=20

    Am I missing something?&nbsp; I am writing a procedure to drop and = recreate=20

    the sequence instead. </FONT></P>
    <P><FONT face=3DArial size=3D2>&nbsp; 1* alter sequence = seq_gn_special_offers_id=20

    nextval =3D 1</FONT> <BR><FONT face=3DArial size=3D2>SQL&gt; = /</FONT> <BR><FONT=20

    face=3DArial size=3D2>alter sequence seq_gn_special_offers_id = nextval =3D 1</FONT>=20

    <BR><FONT face=3DArial=20
    =

size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;=20

    *</FONT> <BR><FONT face=3DArial size=3D2>ERROR at line 1:</FONT> =
<BR><FONT=20

    face=3DArial size=3D2>ORA-02286: no options specified for ALTER = SEQUENCE</FONT>=20

    </P>
    <P><FONT face=3DArial size=3D2>SQL&gt; alter sequence = seq_gn_special_offers_id=20

    set nextval =3D 1;</FONT> <BR><FONT face=3DArial size=3D2>alter = sequence=20

    seq_gn_special_offers_id set nextval =3D 1</FONT> <BR><FONT =

face=3DArial=20
    =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;=20

    *</FONT> <BR><FONT face=3DArial size=3D2>ERROR at line 1:</FONT> =
<BR><FONT=20

    face=3DArial size=3D2>ORA-02286: no options specified for ALTER = SEQUENCE</FONT>=20

    </P>
    <P><B><FONT face=3DArial size=3D1>Lisa Rutland Koivu</FONT></B> =

<BR><FONT=20

    face=3DArial size=3D1>Oracle Database Administrator (do I dare call = myself=20

    that??)</FONT> <BR><FONT face=3DArial size=3D1>Qode.com</FONT> =
<BR><FONT=20

    face=3DArial size=3D1>4850 North State Road 7</FONT> <BR><FONT = face=3DArial=20

    size=3D1>Suite G104</FONT> <BR><FONT face=3DArial size=3D1>Fort = Lauderdale,=20

    FL&nbsp; 33319</FONT> </P>
    <P><FONT face=3DArial size=3D1>V: 954.484.3191, x174</FONT> =
<BR><FONT face=3DArial=20

    size=3D1>F: 954.484.2933 </FONT><BR><FONT face=3DArial size=3D1>C:=20     954.658.5849</FONT> <BR><FONT face=3DArial size=3D1><A=20     href=3D"http://www.qode.com" =
target=3D_blank>http://www.qode.com</A></FONT> </P>

    <P><I><FONT color=3D#000000 face=3DArial size=3D1>"The information = contained=20

    herein does not express the opinion or position of Qode.com and = Received on Thu Sep 07 2000 - 14:00:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US