Re: unsequential sequences

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 02 Aug 2005 20:22:45 +0100
Message-ID: <mihve11r0cvrequva1un15qeos1fanvcm0@4ax.com>

On Mon, 01 Aug 2005 16:43:35 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>HansF wrote:
>
>> Oracle might skip sequence numbers if you choose to cache a set of
>> sequence numbers. For example, when an instance abnormally shuts down (for
>> example, when an instance failure occurs or a SHUTDOWN ABORT statement is
>> issued), sequence numbers that have been cached but not used are lost.
>
>Interestingly enough, with 10gR1, I have tried to create a demonstration
>to show this and have failed every time. Somehow, it seems, no numbers
>are lost. Has anyone else seen this? Thanks.

Sequence cache appears to be discarded as expected in 10gR1 and 10gR2.

You weren't perhaps doing a clean shutdown instead of an abort? That _does_ write the cache back to the sequence so the numbers aren't lost.

I've pasted the 10gR1 runs below (using abort first, then using immediate), results were also identical on R2.

[oracle_at_testbox tmp]$ sqlplus /nolog @lost_sequence_numbers.sql

SQL*Plus: Release 10.1.0.4.0 - Production on Tue Aug 2 20:18:30 2005

SQL> connect test/test
Connected.
SQL> create sequence testseq cache 20;

Sequence created.

SQL> select testseq.nextval from dual;

NEXTVAL

SQL> select sequence_name, cache_size, last_number 2 from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TESTSEQ                                20          21

SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 180355072 bytes

Fixed Size                   778372 bytes
Variable Size             108011388 bytes
Database Buffers           71303168 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.
SQL> connect test/test
Connected.
SQL> select testseq.nextval from dual;

NEXTVAL

SQL> drop sequence testseq;

Sequence dropped.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning and Data Mining options

[... another run but using immediate instead of abort ...]

[oracle_at_testbox tmp]$ sqlplus /nolog @lost_sequence_numbers.sql

SQL*Plus: Release 10.1.0.4.0 - Production on Tue Aug 2 20:16:44 2005

SQL> connect test/test
Connected.
SQL> create sequence testseq cache 20;

Sequence created.

SQL> select testseq.nextval from dual;

NEXTVAL

SQL> select sequence_name, cache_size, last_number 2 from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TESTSEQ                                20          21

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 180355072 bytes

Fixed Size                   778372 bytes
Variable Size             108011388 bytes
Database Buffers           71303168 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.
SQL> connect test/test
Connected.
SQL> select testseq.nextval from dual;

NEXTVAL

SQL> drop sequence testseq;

Sequence dropped.

SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning and Data Mining options

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Tue Aug 02 2005 - 14:22:45 CDT