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: Steve McClure <steve_at_pactr.com>
Date: Thu, 7 Sep 2000 11:28:54 -0700
Message-Id: <10612.116433@fatcity.com>


This is a multi-part message in MIME format.

------=_NextPart_000_0016_01C018BE.CE3E0DC0 Content-Type: text/plain;

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

ALTER SEQUENCE NEXTVALLisa,

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.=20

    1* alter sequence seq_gn_special_offers_id nextval =3D 1=20   SQL> /=20
  alter sequence seq_gn_special_offers_id nextval =3D 1=20

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

  SQL> alter sequence seq_gn_special_offers_id set nextval =3D 1;=20   alter sequence seq_gn_special_offers_id set nextval =3D 1=20

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

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

  V: 954.484.3191, x174=20
  F: 954.484.2933=20
  C: 954.658.5849=20

  http://www.qode.com=20

  "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_000_0016_01C018BE.CE3E0DC0 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><TITLE>ALTER SEQUENCE NEXTVAL</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<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 the=20
sequence and recreated it with the 'start with' value I = 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"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-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 title=3Dlkoivu_at_qode.com href=3D"mailto:lkoivu_at_qode.com">Koivu, = Lisa</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
  </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; Am=20
  I missing something?&nbsp; I am writing a procedure to drop and = recreate the=20
  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;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&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 set=20
  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;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&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, FL&nbsp;=20
  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 target=3D_blank =

  href=3D"http://www.qode.com">http://www.qode.com</A></FONT> </P>   <P><I><FONT face=3DArial color=3D#000000 size=3D1>"The information = contained herein=20
  does not express the opinion or position of Qode.com and cannot be = Received on Thu Sep 07 2000 - 13:28:54 CDT

Original text of this message

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