Oracle 10g sequence management basics: creation and modification

Sequence management is commonly used for Oracle 10 Gb system management. The following describes sequence management.

In oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.

1. Create Sequence for Oracle 10 Gb Sequence Management

First, you must have the create sequence or create any sequence permission,

 
 

  
  
CREATE SEQUENCEEmp_sequence
  
  
INCREMENTBY1-- Add a few 
  
  
STARTWITH1-- Count from 1 
  
  
NOMAXVALUE-- Do not set the maximum value 
  
  
NOCYCLE-- Always accumulate, not loop 
  
  
CACHE 10;
 
 

Once emp_sequence is defined, you can use CURRVAL, NEXTVAL

CURRVAL = returns the current sequence Value

NEXTVAL = increase the sequence value, and then return the sequence Value

For example:

 
 

  
  
emp_sequence.CURRVAL   
  
  
emp_sequence.NEXTVAL  
 
 

Where sequence can be used:

-SELECT statements that do not contain subqueries, snapshot, and VIEW

-The INSERT statement is in the subquery.

-In the value of the NSERT statement

-UPDATE in SET

See the following example:

 
 

  
  
INSERT INTO emp VALUES     
  
  
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);   
  
  
 
  
  
SELECT empseq.currval      FROM DUAL;   
 
 

Note that:

-The first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment by and then returns the added value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error will occur. NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand?

-If the CACHE value is specified, ORACLE can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to shut down and shut down abort), the sequence in the cache will be lost. Therefore, you can use nocache when creating sequence to prevent this situation.

2. Alter Sequence for Oracle 10g Sequence Management

You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.

Alter sequence example

 
 

  
  
ALTER SEQUENCEEmp_sequence
  
  
INCREMENTBY10
  
  
Max value 10000
  
  
CYCLE-- Start from scratch after 10000 
  
  
NOCACHE;
 
 

Initialization parameters that affect Sequence:

SEQUENCE_CACHE_ENTRIES = sets the number of sequence that can be simultaneously cached.

It's easy to Drop Sequence.

 
 

  
  
DROP SEQUENCE order_seq;  
 
 

The above is the basic knowledge of Oracle 10g sequence management.

1. How to unlock users in Oracle
2. Oracle username re-indexing method
3. Explain how to query user tablespaces in Oracle
4. An analysis of the management method of the Oracle user permission table
5. Introduction to Oracle database backup and recovery features