| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> sequence omits numbers?
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.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.comReceived on Tue Aug 05 2003 - 07:25:12 CDT
-------------------------------------
 |
 |