put a few rollbacks into your test and more than one concurrent session and
see what happens.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
news:E2F6A70FE45242488C865C3BC1245DA702648162_at_lnewton.leeds.lfs.co.uk...
> Chao,
>
> are you aware that sequences can and do 'lose' values and should not be
> relied upon to produce an unbroken sequence of numbers?
> The following is an example :
>
> SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jul 19 10:02:33 2002
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
> With the Partitioning option
> JServer Release 8.1.7.4.0 - 64bit Production
>
> SQL> create sequence test cache 20;
> Sequence created.
>
> SQL> select test.nextval from dual;
>
> NEXTVAL
> ----------
> 1
>
> <repeat a few times - just for fun !>
>
> SQL> r
> 1* select test.nextval from dual
>
> NEXTVAL
> ----------
> 6
>
> SQL> connect system_at_cmdb
> Enter password: ******
> Connected.
> SQL> alter system flush shared_pool;
>
> System altered.
>
> SQL> connect cmdb_at_cmdb
> Enter password: ******
> Connected.
>
> SQL> select test.nextval from dual;
>
> NEXTVAL
> ----------
> 21
>
> SQL> exit
>
>
> You can pin the sequences in the shared pool to avoid this, but even
> then, when you shut down and restart again, the cached values are lost.
> If you haven't already done so, you'll need to run
> $ORACLE_HOME/rdbms/admin/dbmspool.sql to create the package - as
> internal or sys.
>
>
> SQL> connect sys_at_cmdb
> Enter password: ********
> Connected.
>
> SQL> execute dbms_shared_pool.keep('CMDB.TEST','Q');
>
> PL/SQL procedure successfully completed.
>
> SQL> select cmdb.test.nextval from dual;
>
> NEXTVAL
> ----------
> 22
>
> SQL> alter system flush shared_pool;
>
> System altered.
>
> SQL> select cmdb.test.nextval from dual;
>
> NEXTVAL
> ----------
> 23
>
>
>
> But, this doesn't save the sequence numbers being lost when you shutdown
> :
>
> <insert shutdown/startup here>
>
> SQL> select cmdb.test.nextval from dual;
>
> NEXTVAL
> ----------
> 24
>
>
> OH !
> OK, maybe Oracle 8.1.7.4 does preserve sequence numbers over a restart
> then. But remember, after every restart you'll need to re-pin the
> sequence to avoid losses.
>
>
>
> HTH
>
> Regards,
> Norman.
>
> -------------------------------------
> 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
> -------------------------------------
>
>
Received on Fri Jul 19 2002 - 06:32:20 CDT
