你好,游客 登录 注册 搜索
背景:
阅读新闻

Sequence in Oracle DB - PTIAN ORACLE EBS REPOSITORY

[日期:2013-04-13] 来源:  作者: [字体: ]

A sequence is a database object that generates unique numbers, mostly used for primary key values.

One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.


Create a sequence

CREATE SEQUENCE INV.MTL_MATERIAL_TRANSACTIONS_S
  START WITH 22611295
  MAXVALUE 4294967295
  MINVALUE 1
  NOCYCLE
  CACHE 1000
  NOORDER;

Selecting from the sequence:

SQL> select MTL_MATERIAL_TRANSACTIONS_S.nextval from dual;
   NEXTVAL
----------
   24177354

SQL> select MTL_MATERIAL_TRANSACTIONS_S.nextval from dual;
   NEXTVAL
----------
   24177355


Sequence Related Queries

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME = 'MTL_MATERIAL_TRANSACTIONS_S';

SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = 'MTL_MATERIAL_TRANSACTIONS_S';

Key Fields in DBA_SEQUENCES

  SEQUENCE_OWNER
      Name of the owner of the sequence
   SEQUENCE_NAME
      SEQUENCE name
   MIN_VALUE
      Minimum value of the sequence
   MAX_VALUE
      Maximum value of the sequence
   INCREMENT_BY
      Value by which sequence is incremented
   CYCLE_FLAG
      Does sequence wrap around on reaching limit?
   ORDER_FLAG
      Are sequence numbers generated in order?
   CACHE_SIZE
      Number of sequence numbers to cache
   LAST_NUMBER
      Last sequence number written to disk

DBA_SEQUENCES.LAST_NUMBER

LAST_NUMBER is influenced by the CACHE size. I saw that my LAST_NUMBER was 21 (my cache was 20). When I ran NEXTVAL I was getting a number like 4, then 5. I went up past 20 and my LAST_NUMBER jumped to 41 so everytime the cached numbers ran out another group of cached numbers would be reserved and the LAST_NUMBER field would change, but this has no relation to the CURRVAL.


Reference:

Frequently Asked Questions About Sequences (Doc ID 108643.1)

Determining appropriate Sequence Cache value :http://srivenukadiyala.wordpress.com/2010/12/09/determining-appropriate-sequence-cache-value/

http://psoug.org/reference/sequences.html





收藏 推荐 打印 | 录入:admin | 阅读:
相关新闻