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

Home -> Community -> Usenet -> c.d.o.misc -> Re: sequence omits numbers?

Re: sequence omits numbers?

From: Stefan Röhle <roehle_at_uni-mainz.de>
Date: Tue, 05 Aug 2003 16:29:48 +0200
Message-ID: <bgof1d$5ra$2@bambi.zdv.Uni-Mainz.DE>


In the meantime I came across that and I have changed the sequence to "nocache". I hope that will leave the gaps smaller.

Norman Dunbar wrote:
> Stephan,
>
> First of all, a sequence CANNOT be used to build a complete set of
> numbers - because, sequnces can lose numbers under certain conditions.
>
> consider the following :
>
> create sequence testseq
> order
> cache 10;
>
> The 'cache 10' says to Oracle, whenever the first number is requested
> from this sequence, stick the next 9 into the cache so that subsequent
> requests for numbers from this sequence will work much faster.
>
> Unfortunately, when the database is closed down (shutdown abort, normal
> or immediate, or startup force) then all cahced values are lost. In
> addition, if the DBA flushes the shared pool, you lose the cached
> numbers as well.
>
> You can pin the sequence in the SGA using
> SYS.DBMS_SHARED_POOL.KEEP('TESTSEQ','Q') (assuming that SYS has run the
> $ORACLE_HOME/rdbms/admin/dbmspool.sql script and granted your user (or
> PUBLIC) execute privs).
>
> Now, you don't lose the cached numbers when the shared pool is flushed,
> or when the database is shutdown (normal or immediate) but you will
> still lose them if the database is shutdown abort or startup forced.
>
> As I said, you cannot guarantee not to lose numbers from a sequence.
>
> Cheers,
> Norm.
>
> PS. Startup force is equivalent to shutdown abort followed by startup.
> PPS. There is a thread on comp.database.oracle.server on sequences and
> pinning at present which might give further information.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>

-- 
*******************************
Stefan Röhle
Zentrum für Datenverarbeitung
Johannes Gutenberg-Universität
D-55099 Mainz
Germany

Tel. +49-(0)6131/39-26303
Fax. +49-(0)6131/39-26407
Email: roehle_at_uni-mainz.de
*******************************
Received on Tue Aug 05 2003 - 09:29:48 CDT

Original text of this message

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