SEQUENCE in Oracle

SEQUENCE in Oracle

• SEQUENCE is used to generate unique numbers automatically in oracle
• It is a database object
• It is used to generally create PRIMARY KEY values

CREATE SEQUENCE Statement description:

Keywords related to CREATE SEQUENCE statement:

• INCREMENT BY
• STARTS WITH
• MINVALUE
• MAXVALUE
• CYCLE
• NOCYCLE
• CACHE
• NOCACHE

INCREMENT BY:

It represents the difference between current sequence value and the next sequence value.

STARTS WITH:

It specifies the first sequence number to be generated by oracle for a given sequence. If this clause is excluded then default is 1.

MINVALUE:

It specifies the minimum value to be allowed for a sequence. If this clause is excluded then default is 1.

MAXVALUE:

It specifies the maximum value to be allowed for a sequence. if this clause is excluded then default is 9999999999999999999999999999.

CYCLE:

It specifies that whether the sequence will continue to generate unique numbers after reaching its maximum value.

NOCYCLE:

It specifies that whether the sequence will not continue to generate unique numbers after reaching its maximum value. NOCYCLE is the default.

CACHE:

It specifies that how many numbers oracle server will pre-allocate and keep in memory for a sequence. This is the default option. Oracle server caches 20 values by default.

NOCACHE:

It specifies that oracle server will not cache any value for a given sequence.

Creating a SEQUENCE:

WITHOUT USING ANY CREATE SEQUENCE CLAUSES:

CREATE SEQUENCE EMP_TEST_SEQ;

Verifying the sequence:

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ';

USING CREATE SEQUENCE CLAUSES:

CREATE SEQUENCE EMP_TEST_SEQ_CLAUSES
                INCREMENT BY 5
                START WITH 100
                MINVALUE 90
                MAXVALUE 150
                CYCLE
                NOCACHE;

Verifying data dictionary:

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

Accessing a SEQUENCE:

We can access a SEQUENCE with below two psedocolumns:

• NEXTVAL
• CURRVAL

NEXTVAL:

It is returned the next possible unique value for sequence.
On first time access NEXTVAL returns the value defined against START WITH clause.

SELECT EMP_TEST_SEQ_CLAUSES.NEXTVAL FROM DUAL;

On subsequent access NEXTVAL returns the next unique numbers based on INCREMENT BY clause.

CURRVAL:

It returns the current sequence value.

SELECT EMP_TEST_SEQ_CLAUSES.CURRVAL FROM DUAL;

For a newly created sequence NEXTVAL to be accessed first. If we access CURRVAL first, then oracle returns error.

SELECT EMP_TEST_SEQ.CURRVAL FROM DUAL;

SELECT EMP_TEST_SEQ.NEXTVAL FROM DUAL;

SELECT EMP_TEST_SEQ.CURRVAL FROM DUAL;

LAST_NUMBER COLUMN:

LAST_NUMBER of column of USER_SEQUNCES data dictionary view indicates next possible unique value to be generated by a given sequence. But there is one condition that the sequence must be created with NOCACHE clause. If CACHE is used then LAST_NUMBER returns the next possible number after CACHE limit.

SELECT EMP_TEST_SEQ_CLAUSES.NEXTVAL FROM DUAL;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

Usages of SEQUENCE:

• Sequence can be used to set the default value of a PRIMARY KEY column.
• If we use CACHE in CREATE SEQUENCE statement then sequence values are received from memory so that response time will be faster.

SEQUENCE as default value:

CREATE SEQUENCE EMPID_SEQ;

Creating table with the above sequence:

CREATE TABLE EMP_SEQ_TEST
(EMPLOYEE_ID   NUMBER(6) DEFAULT EMPID_SEQ.NEXTVAL,
 FIRST_NAME    VARCHAR2(30),
 LAST_NAME     VARCHAR2(30),
 JOB_ID        VARCHAR2(10),
 SALARY        NUMBER(8, 2),
 DEPARTMENT_ID NUMBER(4),
 CONSTRAINT EMPID_PK PRIMARY KEY(EMPLOYEE_ID)
);

Inserting data into EMP_SEQ_TEST table:

INSERT INTO EMP_SEQ_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) 
VALUES (DEFAULT,'Steven','King','AD_PRES',24000,90);

CACHE SEQUENCE value:

If we use CACHE in sequence then the CACHE is populated in memory when you first time refer to the sequence. Then after every reference to the sequence, values are retrieved from memory till the CACHE limit is completed. After the last sequence value of current CACHE is used then the next reference to the sequence populate the next CACHE into memory.

Drawback of SEQUENCE:

GAP in SEQUENCE value:

We have seen that sequence can be used in INSERT operation. But if due to any reason if we rollback the transaction that does not impact the sequence value.
Sequence value can not be rolled back. So there will be a gap created after every rollback transaction where sequence is used. This is a drawback of sequence.

In the previous example if we rollback the insert statement and then again trying to insert the same record then the EMPLOYEE_ID will be stamped as 2.

ROLLBACK;

INSERT INTO EMP_SEQ_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) 
VALUES (DEFAULT,'Steven','King','AD_PRES',24000,90);

SELECT * FROM EMP_SEQ_TEST;

There are other scenarios where gap in sequence can be created:

• System crash
• If the same sequence is used in multiple tables.

Modifying a SEQUENCE:

We can use ALTER SEQUENCE option to modify an existing sequence.
ALTER SEQUENCE can modify all the clauses of a CREATE SEQUENCE statement except START WITH.

CREATE SEQUENCE EMP_TEST_SEQ_CLAUSES
                INCREMENT BY 5
                START WITH 100
                MINVALUE 90
                MAXVALUE 150
                CYCLE
                NOCACHE;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

In the above created sequence we want to change the MIN_VALUE to 50, MAX_VALUE to 300, INCREAMENT_BY to 10, CYCLE_FLAG to NO.

ALTER SEQUENCE EMP_TEST_SEQ_CLAUSES
                INCREMENT BY 10
                MINVALUE 50
                MAXVALUE 300
                NOCYCLE
                NOCACHE;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

Some time ALTER SEQUENCE is used to fill up the gap created by transaction failure or rolling back the transactions.

In the above example lets say we want to start the sequence value from 60. So we will do the following steps.

Sequence LAST_NUMBER is 105 and INCREMENT_BY is 10. So the current value is 95. So we will reduce 35 from current value.

ALTER SEQUENCE EMP_TEST_SEQ_CLAUSES
INCREMENT BY -35;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

So we can see that now the LAST_NUMBER is 60. That means next value will be 60.

SELECT EMP_TEST_SEQ_CLAUSES.NEXTVAL FROM DUAL;

Now we will again use ALTER SEQUENCE to INCREMENT BY 10.

ALTER SEQUENCE EMP_TEST_SEQ_CLAUSES
INCREMENT BY 10;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_CLAUSES';

SELECT EMP_TEST_SEQ_CLAUSES.CURRVAL FROM DUAL;

Types of SEQUENCE:

• GLOBAL SEQUENCE
• SESSION SEQUENCE

GLOBAL SEQUENCE:

The sequences we have discussed so far is the GLOBAL SEQUENCES. This is the default category.
It means that the sequence value is not dependent on a session only. It can be accessed by all sessions with sequential incremental values.

We can identify a GLOBAL SEQUENCE with the SESSION_FLAG of USER_SEQUENCES data dictionary view.
If SESSION_FLAG is N then its a GLOBAL SEQUENCE. In the all above examples the SESSION_FLAG is N.

There is a hidden GLOBAL keyword in the CREATE SEQUENCE statement. We can explicitly write that as well in CREATE SEQUENCE statement.

CREATE SEQUENCE EMP_TEST_SEQ_GLOBAL GLOBAL
                INCREMENT BY 5
                START WITH 100
                MINVALUE 90
                MAXVALUE 150
                CYCLE
                NOCACHE;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_GLOBAL';

SESSION SEQUENCE:

SESSION SEQUENCE last only for a session only. It can be used by multiple sessions but all the sessions have their own individual sequential values for the same sequence.

We have to explicitly mention the SESSION keyword in CREATE SEQUENCE statement.

We can identify a SESSION SEQUENCE with the SESSION_FLAG of USER_SEQUENCES data dictionary view.
If SESSION_FLAG is Y then its a SESSION SEQUENCE.

SESSION SEQUENCE is useful when we require to populate a temporary table data within a session only.

CREATE SEQUENCE EMP_TEST_SEQ_SESSION SESSION
                INCREMENT BY 5
                START WITH 100
                MINVALUE 90
                MAXVALUE 150
                CYCLE
                NOCACHE;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_SESSION';

Converting a GLOBAL SEQUENCE into a SESSION SEQUENCE:

ALTER SEQUENCE EMP_TEST_SEQ_GLOBAL SESSION;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_GLOBAL';

Converting a SESSION SEQUENCE into a GLOBAL SEQUENCE:

ALTER SEQUENCE EMP_TEST_SEQ_SESSION GLOBAL;

SELECT SEQUENCE_NAME, 
       MIN_VALUE, 
       MAX_VALUE, 
       INCREMENT_BY, 
       CYCLE_FLAG, 
       CACHE_SIZE, 
       LAST_NUMBER, 
       SESSION_FLAG, 
       KEEP_VALUE 
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'EMP_TEST_SEQ_SESSION';

Remove a SEQUENCE:

DROP SEQUENCE EMP_TEST_SEQ_CLAUSES; 

RELATED TOPICS:

• How to increase sequence value by 100 in Oracle?