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@xxxxxxxxx> To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx Cc: "ilmar.kerm@xxxxxxxxx" <ilmar.kerm@xxxxxxxxx>, Oracle-L Freelists=20 <oracle-l@xxxxxxxxxxxxx> Date: 03/29/2012 08:45 AM Subject: Re: HOW TO SET SEQUENCE VALUE Sent by: oracle-l-bounce@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [ mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 > > -- > //www.freelists.org/webpage/oracle-l > > --=20 Howard A. Latham -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l