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: unsequential sequences

Re: unsequential sequences

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 02 Aug 2005 15:50:08 -0700
Message-ID: <1123022994.538534@yasure>


Andy Hassall wrote:
> 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
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> SQL> connect test/test
> Connected.
> SQL> create sequence testseq cache 20;
>
> Sequence created.
>
> SQL> select testseq.nextval from dual;
>
> NEXTVAL
> ----------
> 1
>
> 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
> ----------
> 21
>
> 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
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> SQL> connect test/test
> Connected.
> SQL> create sequence testseq cache 20;
>
> Sequence created.
>
> SQL> select testseq.nextval from dual;
>
> NEXTVAL
> ----------
> 1
>
> 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
> ----------
> 2
>
> 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

Working now. Perhaps I wasn't using abort ... but I was so .... sure.

Thanks.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Tue Aug 02 2005 - 17:50:08 CDT

Original text of this message

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