Re: Oracle sequences

This from my notes but you should really get the Oracle documentation from their web site.

Van

Sequences

 You may wish to select from a sequence across a database link. The syntax for doing this is somewhat unusual. The name of the sequence must specify the name of the link, and so must the dual table, like this: select sequence_name.nextval_at_database_link from dual_at_database_link;
Also if you didn't have a synonym on the remote database for the schema.sequence_name you would have to specify: select schema.sequence_name.nextval_at_database_link from dual_at_database_link;

The sequence generator provides a sequential series of numbers. The sequence generator is especially
useful in multi-user environments for generating unique sequential numbers without the overhead of disk
I/O or transaction locking. Therefore, the sequence generator reduces "serialization" where the
statements of two transactions must generate sequential numbers at the same time. By avoiding the
serialization that results when multiple users wait for each other to generate and use a sequence number,
the sequence generator improves transaction throughput and a user's wait is considerably shorter.

Sequence numbers are Oracle integers defined in the database of up to 38 digits. A sequence definition
indicates general information: the name of the sequence, whether it ascends or descends, the interval
between numbers, and other information. One important part of a sequence's definition is whether
Oracle should cache sets of generated sequence numbers in memory. Oracle stores the definitions of all
sequences for a particular database as rows in a single data dictionary table in the SYSTEM tablespace.
Therefore, all sequence definitions are always available, because the SYSTEM tablespace is always
online.

Sequence numbers are used by SQL statements that reference the sequence. You can issue a statement
to generate a new sequence number or use the current sequence number. Once a statement in a user's
session generates a sequence number, the particular sequence number is available only to that session;
each user that references a sequence has access to its own, current sequence number.

Sequence numbers are generated independently of tables. Therefore, the same sequence generator can
be used for one or for multiple tables. Sequence number generation is useful to generate unique primary
keys for your data automatically and to coordinate keys across multiple rows or tables. Individual
sequence numbers can be skipped if they were generated and used in a transaction that was ultimately
rolled back. Applications can make provisions to catch and reuse these sequence numbers, if desired.

SEQUENCE_CACHE_ENTRIES
Default value: 10
Range of values: 10 - 32000
Multiple instances: can have different values The number of sequences that can be cached in the SGA for immediate access. This cache is managed
on a least recently used (LRU) basis, so if a request is made for a sequence that is not in the cache and
there are no free entries, the oldest one on the LRU list is deleted and replaced with the newly requested
one. Highest concurrency is achieved when this value is set to the highest possible number of sequences
that will be used on an instance at one time. Each entry requires approximately 110 bytes in the SGA for an Oracle7 Parallel Server.
Sequences created with the NOCACHE option do not reside in this cache. They must be written
through to the data dictionary on every use.

Managing Sequences

The sequence generator generates sequential numbers. Sequence number generation is useful to generate
unique primary keys for your data automatically, and to coordinate keys across multiple rows or tables.
Without sequences, sequential values can only be produced programmatically. A new primary key value
can be obtained by selecting the most recently produced value and incrementing it. This method requires
a lock during the transaction and causes multiple users to wait for the next value of the primary key; this
waiting is known as serialization. If you have such constructs in your applications, you should replace
them with access to sequences. Sequences eliminate serialization and improve the concurrency of your
application.

Creating Sequences

To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege; to
create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE privilege.
Create a sequence using the SQL command CREATE SEQUENCE. For example, the following
statement creates a sequence used to generate employee numbers for the EMPNO column of the EMP
table:

CREATE SEQUENCE emp_sequence

   INCREMENT BY 1
   START WITH 1
   NOMAXVALUE
   NOCYCLE
   CACHE 10; The CACHE option of the CREATE SEQUENCE command pre-allocates a set of sequence numbers
and keeps them in memory so that they can be accessed faster. When the last of the sequence numbers
in the cache have been used, another set of numbers is read into the cache. Notice that several parameters can be specified to control the function of sequences. You can use these
parameters to indicate whether the sequence is ascending or descending, the starting point of the sequence, the minimum and maximum values, and the interval between sequence values. The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.
To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege. To
create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE privilege.
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. Also, sequence numbers that have been used but not saved are lost as well. Oracle might also skip
cached sequence numbers after an export and import; see the Oracle7 Server Utilities guide for
details.

Altering Sequences

To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY
SEQUENCE system privilege. You can alter a sequence to change any of the parameters that define
how it generates sequence numbers except the sequence's starting number. To change the starting point
of a sequence, drop the sequence and then re-create it. Alter a sequence using the SQL command ALTER SEQUENCE. For example, the following statement
alters the EMP_SEQUENCE:

ALTER SEQUENCE emp_sequence

   INCREMENT BY 10
   MAXVALUE 10000
   CYCLE
   CACHE 20; Initialization Parameters Affecting Sequences

The initialization parameter SEQUENCE_CACHE_ENTRIES sets the number of sequences that may
be cached at any time. If auditing is enabled for your system, allow one additional sequence for the
sequence to identify audit session numbers. If the value for SEQUENCE_CACHE_ENTRIES is too low, Oracle might skip sequence values, as in
the following scenario: assume you are using five cached sequences, the cache is full, and
SEQUENCE_CACHE_ENTRIES = 4. If four sequences are currently cached, then a fifth sequence
replaces the least recently used sequence in the cache and all remaining values (up to the last sequence
number cached) in the displaced sequence are lost.

Dropping Sequences

You can drop any sequence in your schema. To drop a sequence in another schema, you must have the
DROP ANY SEQUENCE system privilege. If a sequence is no longer required, you can drop the
sequence using the SQL command DROP SEQUENCE. For example, the following statement drops
the ORDER_SEQ sequence:

DROP SEQUENCE order_seq;

When a sequence is dropped, its definition is removed from the data dictionary. Any synonyms for the
sequence remain, but return an error when referenced.

Using Sequences

The following sections provide some information on how to use a sequence once it has been defined.
Once defined, a sequence can be made available to many users. A sequence can be accessed and
incremented by multiple users with no waiting. Oracle does not wait for a transaction that has
incremented a sequence to complete before that sequence can be incremented again.
The examples outlined in the following sections show how sequences can be used in master/detail table
relationships. Assume an order entry system is partially comprised of two tables, ORDERS (master
table) and LINE_ITEMS (detail table), that hold information about customer orders. A sequence named
ORDER_SEQ is defined by the following statement:

CREATE SEQUENCE order_seq

   START WITH 1
   INCREMENT BY 1
   NOMAXVALUE
   NOCYCLE
   CACHE 20; Referencing a Sequence

A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudocolumns;
each new sequence number is generated by a reference to the sequence's pseudocolumn NEXTVAL,
while the current sequence number can be repeatedly referenced using the pseudo-column CURRVAL.
NEXTVAL and CURRVAL are not reserved words or keywords and can be used as pseudo-column
names in SQL statements such as SELECTs, INSERTs, or UPDATEs. Generating Sequence Numbers with NEXTVAL To generate and use a sequence number,
reference seq_name.NEXTVAL. For example, assume a customer places an order. The sequence
number can be referenced in a values list, as in

INSERT INTO orders (orderno, custno)

   VALUES (order_seq.NEXTVAL, 1032);

or in the SET clause of an UPDATE statement, as in

UPDATE orders

   SET orderno = order_seq.NEXTVAL
   WHERE orderno = 10112;

or the outermost SELECT of a query or subquery, as in

SELECT order_seq.NEXTVAL FROM dual;

As defined, the first reference to ORDER_SEQ.NEXTVAL returns the value 1. Each subsequent
statement that references ORDER_SEQ.NEXTVAL generates the next sequence number (2, 3, 4, . . .).
The pseudo-column NEXTVAL can be used to generate as many new sequence numbers as necessary.
However, only a single sequence number can be generated per row; that is, if NEXTVAL is referenced
more than once in a single statement, the first reference generates the next number and all subsequent
references in the statement return the same number. Once a sequence number is generated, the sequence number is available only to the session that
generated the number. Independent of transactions committing or rolling back, other users referencing
ORDER_SEQ.NEXTVAL obtain unique values. If two users are accessing the same sequence
concurrently, the sequence numbers each user receives might have gaps because sequence numbers are
also being generated by the other user.
Using Sequence Numbers with CURRVAL To use or refer to the current sequence value of your
session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has
been referenced in the current user session (in the current or a previous transaction). CURRVAL can be
referenced as many times as necessary, including multiple times within the same statement. The next
sequence number is not generated until NEXTVAL is referenced. Continuing with the previous example,
you would finish placing the customer's order by inserting the line items for the order:

INSERT INTO line_items (orderno, partno, quantity)

   VALUES (order_seq.CURRVAL, 20321, 3);

INSERT INTO line_items (orderno, partno, quantity)

   VALUES (order_seq.CURRVAL, 29374, 1);

Assuming the INSERT statement given in the previous section generated a new sequence number of
347, both rows inserted by the statements in this section insert rows with order numbers of 347.
Uses and Restrictions of NEXTVAL and CURRVAL CURRVAL and NEXTVAL can be used in
the following places:

     VALUES clause of INSERT statements
     the SELECT list of a SELECT statement
     the SET clause of an UPDATE statement

CURRVAL and NEXTVAL cannot be used in these places:
     a subquery
     a view's query or snapshot's query
     a SELECT statement with the DISTINCT operator
     a SELECT statement with a GROUP BY or ORDER BY clause
     a SELECT statement that is combined with another SELECT statement with
the UNION,
     INTERSECT, or MINUS set operator
     the WHERE clause of a SELECT statement
     DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
     the condition of a CHECK constraint

Caching Sequence Numbers

Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence
numbers can be accessed more quickly in the sequence cache than they can be read from disk.
The sequence cache consists of entries. Each entry can hold many sequence numbers for a single
sequence.
Follow these guidelines for fast access to all sequence numbers: Be sure the sequence cache can hold all the sequences used concurrently by your applications.
Increase the number of values for each sequence held in the sequence cache. The Number of Entries in the Sequence Cache When an application accesses a sequence in the
sequence cache, the sequence numbers are read quickly. However, if an application accesses a
sequence that is not in the cache, the sequence must be read from disk to the cache before the sequence
numbers are used.
If your applications use many sequences concurrently, your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications. The number of entries in the sequence cache is determined by the initialization parameter
SEQUENCE_CACHE_ENTRIES. The default value for this parameter is 10 entries. Oracle creates
and uses sequences internally for auditing, grants of system privileges, grants of object privileges,
profiles, debugging stored procedures, and labels. Be sure your sequence cache has enough entries to
hold these sequences as well as sequences used by your applications. If the value for your SEQUENCE_CACHE_ENTRIES parameter is too low, it is possible to skip
sequence values. For example, assume that this parameter is set to 4, and that you currently have four
cached sequences. If you create a fifth sequence, it will replace the least recently used sequence in the
cache. All of the remaining values in this displaced sequence are lost. That is, if the displaced sequence
originally held 10 cached sequence values, and only one had been used, nine would be lost when the
sequence was displaced.
The Number of Values in Each Sequence Cache Entry When a sequence is read into the sequence
cache, sequence values are generated and stored in a cache entry. These values can then be accessed
quickly. The number of sequence values stored in the cache is determined by the CACHE parameter in
the CREATE SEQUENCE statement. The default value for this parameter is 20. This CREATE SEQUENCE statement creates the SEQ2 sequence so that 50 values of the sequence
are stored in the SEQUENCE cache:

CREATE SEQUENCE seq2

   CACHE 50 The first 50 values of SEQ2 can then be read from the cache. When the 51st value is accessed, the next
50 values will be read from disk.
Choosing a high value for CACHE allows you to access more successive sequence numbers with fewer
reads from disk to the sequence cache. However, if there is an instance failure, all sequence values in the
cache are lost. Cached sequence numbers also could be skipped after an export and import if
transactions continue to access the sequence numbers while the export is running.
If you use the NOCACHE option in the CREATE SEQUENCE statement, the values of the sequence
are not stored in the sequence cache. In this case, every access to the sequence requires a disk read.
Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3
sequence so that its values are never stored in the cache:

CREATE SEQUENCE seq3

   NOCACHE "Norddin HABTI" <logsys_at_tanja.net.ma> wrote in message news:W0zH5.76$%3.12_at_news.iam.net.ma...
> how can i use oracle sequences ?
> please i want some exemples.
> thanks.
>
>
Received on Thu Oct 19 2000 - 20:06:56 CDT