Millions of sequence in a database to record the counter for mill ions of products?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 19 Jul 2002 10:37:57 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702648162@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 - 04:37:57 CDT