Re: HOW TO SET SEQUENCE VALUE

From: <TESTAJ3_at_nationwide.com>
Date: Thu, 29 Mar 2012 09:03:13 -0400
Message-ID: <OF70504129.BCCAAD16-ON852579D0.004768E0-852579D0.0047B519_at_lnotes-gw.ent.nwie.net>

Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable The numbers even cached ones are ONLY lost in the event the instance is=20 aborted(either by shutdown abort or dumping). when a shutdown immediate=20 is run, the last number not used is pushed back out so when it comes back=20 up, you would lose none.

As stated by others don't expect continuous numbers, as also a select from =

the sequence and a rollback will lost numbers also.

joe

From:
Howard Latham <howard.latham_at_gmail.com> To:
ChrisDavid.Taylor_at_ingrambarge.com
Cc:
"ilmar.kerm_at_gmail.com" <ilmar.kerm_at_gmail.com>, Oracle-L Freelists=20 <oracle-l_at_freelists.org>
Date:
03/29/2012 08:45 AM
Subject:
Re: HOW TO SET SEQUENCE VALUE
Sent by:
oracle-l-bounce_at_freelists.org

Beware sequence number can be lost even with nocache. You cannot use them as a contiguous numbering system where missing numbers will cause questions. Best rule of thumb don't use them if someone will look at them.

On 29 March 2012 13:27, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> Also be advised that sequences can age out of the cache and lose your=20
cached values. The next time the sequence is called a new cache of=20 numbers is generated and the unused sequence values [before it was aged=20 out] are 'lost'.
> (I wanted to verify that info was correct before I posted so I double=20
checked)
>
>
>
> Chris Taylor
>
> ?Quality is never an accident; it is always the result of intelligent=20
effort.?
> -- John Ruskin (English Writer 1819-1900)
>
> Any views and/or opinions expressed herein are my own and do not=20
necessarily reflect the views of Ingram Industries, its affiliates, its=20 subsidiaries or its employees.
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [
mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ilmar Kerm
> Sent: Thursday, March 29, 2012 7:01 AM
> To: Oracle-L Freelists
> Subject: Re: HOW TO SET SEQUENCE VALUE
>
> Quite dangerous advice, I think... Cache is a very important part of=20
sequence performance (and maybe the default 20 is too low nowadays also).=20 Using NOCACHE should be a very rare occasion, when "losing"
> sequence numbers on instance restarts is not allowed.
> USER=5FSEQUENCES just reports what value is stored in data dictionary, bu=
t=20
database instance is giving out cached sequence numbers. So the difference =

is normal.
>
> Ilmar
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--=20
Howard A. Latham

--
http://www.freelists.org/webpage/oracle-l







--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 29 2012 - 08:03:13 CDT