Re: Caching in Sequence generators

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 22 Apr 1999 20:27:03 +0100
Message-ID: <924809353.19435.0.nnrp-03.9e984b29@news.demon.co.uk>

Not true (at least for recent versions of Oracle); the following is a 'script'ed dialogue:

Script started on Thu Apr 22 20:07:36 1999 $ svrmgrl

Oracle Server Manager Release 2.3.3.0.0 - Production

Oracle7 Server Release 7.3.3.5.1 - Production Release With the distributed and parallel query options PL/SQL Release 2.3.3.5.1 - Production

SVRMGR> connect internal
Connected to an idle instance.
SVRMGR> startup
ORACLE instance started.

Total System Global Area       5933812 bytes
Fixed Size                       38980 bytes
Variable Size                  5042864 bytes
Database Buffers                819200 bytes
Redo Buffers                     32768 bytes

Database mounted.
Database opened.
SVRMGR> create sequence jpl1;
Statement processed.
SVRMGR> select jpl1.nextval from dual;
NEXTVAL

1
1 row selected.
SVRMGR> select jpl1.nextval from dual;
NEXTVAL

2
1 row selected.
SVRMGR> shutdown
Database closed.
Database dismounted.

ORACLE instance shut down.
SVRMGR> startup
SVRMGR> ORACLE instance started.

Total System Global Area       5933812 bytes
Fixed Size                       38980 bytes
Variable Size                  5042864 bytes
Database Buffers                819200 bytes
Redo Buffers                     32768 bytes

Database mounted.
Database opened.
SVRMGR> select jpl1.nextval from dual;
NEXTVAL

3
1 row selected.
SVRMGR> select cache_size from user_sequences where

2> sequence_name = 'JPL1';
CACHE_SIZE

20
1 row selected.

However, your note reminded me that one of the things that makes sequences miss chunks is if they are flushed from the sequence cache (i.e. v$rowcache area), as the following shows:

SVRMGR> create sequence jpl1;
Statement processed.
SVRMGR> select jpl1.nextval from dual;
NEXTVAL

1
1 row selected.
SVRMGR> alter system flush shared_pool; Statement processed.
SVRMGR> select jpl1.nextval from dual;
NEXTVAL

21
1 row selected.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Andrew Babb wrote in message <371F14FB.84C9A1A5_at_mail.com>...
>Yes, simply stopping and starting the database is enough to loose the
Oracle
>sequences. Once one sequence is taken for a particular instance session,
then
>any cached sequences will be lost when the database is closed down
normally. Received on Thu Apr 22 1999 - 14:27:03 CDT