What is Sequences in oracle

What is Sequences in oracle

  • Sequence is a user created object which can be shared by multiple users to generate unique integers
  • The most general use of sequences would be to generate primary key column in the table.
  • The sequence is generated by oracle internal routine so we don’t need to worry about . It will save tim3 as developer don’t need to generate the sequence producing routine
  • A sequential number generator, can be configured to increase or decrease
  • It exist only in the data dictionary, can be limited or repeatable (cycle).
  • We need create sequence privilege to create the sequence

 

Sequences in oracle

Creating a sequence is done using the

CREATE SEQUENCE <seq_name>

[START WITH]

[INCREMENT BY]

[NO/MINVALUE]

[NO/MAXVALUE]

[NO/CYCLE]

[NO/CACHE];

 

 

Description of each values

 

START WITH It defines the initial value of the sequence (default 1),
INCREMENT BY It defines the increase or decrease level (default 1),
MINVALUE It defines the lowest value for a decreasing sequence
MAXVALUE It defines the highest value for an increasing sequence
CYCLE It defines whether the sequence will repeat itself
CACHE It defines the block of sequence numbers held in memory (default 20), NOCACHE forces the data dictionary to be updated for each number generated by the sequence

Examples

 

create sequence test_tech start with 1
increment by 1
maxvalue 10000
cycle
cache 20;

 

 

create sequence test_tech1

start with 1
increment by 1
maxvalue 4500000
cycle
nocache;

 

How to use sequences

In order to use sequence simply use the CURRVAL and NEXTVAL pseudo column

NEXTVAL pseudo column 

It is use to generate the successive sequence number of the specified sequence

CURRVAL pseudo column 

It holds the sequence which the user just generated

 

SELECT TEST_TECH.NEXTVAL FROM DUAL;

 

SELECT TEST_TECH.CURRVAL FROM DUAL;

SELECT TEST_TECH.NEXTVAL FROM DUAL;

 

How to Modify the  sequences

We can modify the sequences using alter sequence.

SQL> ALTER SEQUENCE tech_test1 INCREMENT BY 50;

The changes impact only the future use of the sequence.

You must be the owner or have alter privilege on that sequence

We cannot change start with option. For this sequence need to be dropped  and recreated

Sequence changes are also validated

How to drop the sequence

Dropping a view is done using DROP sequence command.

Drop sequence test_tech  ;

Dictionary Views for seeing the sequence data

sequence details can be queried from the dictionary by querying either USER_SEQUENCES, ALL_ SEQUENCES or DBA_ SEQUENCES. There are three categories of  views

USER_% This view contain information of the objects owned by the user only

Example

USER_TABLES,USER_TAB_COLS

ALL-% This view contains information of the objects which the user can access in the database.

Example

ALL_TABLES,ALL_TAB_COLS

DBA_% This view contain information of the all objects   in the system and these are restricted views which are accessible to the user who have DBA role

Example

DBA_TABLES,DBA_TAB_COLS

 

  DBA_% views about sequences information ALL_% views about sequences information USER_% views about sequences information
View about sequences information dba_sequences all_ sequences user_ sequences

 

To list all sequences owned by the current use

select  sequence_name from user_sequences;

To list all sequences in a database:

Select  owner, sequence_name from dba_sequences;

To list sequences accessible to the current user:

select sequence_name from all_sequences

How to determine the all information about the sequence?

select sequence_name,min_value,max_value,increment_by,last_number
FROM DBA_SEQUENCES

where OWNER = ‘<owner_name>’
and sequence_NAME  = ‘<sequence_name>’;

How to retrieve the current value of an oracle sequence without increment it

The last_number column display the next available sequence number if no cache is specified

SELECT last_number

FROM user_sequences

WHERE sequence_name = ‘<sequence_name>’;

How to extract the sequence definition (DDL statements) from an Oracle database without having to go through a stack of dictionary views

Syntax:

SQL> set long 1000
SQL> set pagesize 0

select DBMS_METADATA.GET_DDL(‘SEQUENCE’,'<sequence_name>’) from DUAL;

 

 

How to  set the LASTVALUE value in an Oracle Sequence

ALTER SEQUENCE tech_seq_name INCREMENT BY 250;

SELECT tech_seq_name.nextval FROM dual;

ALTER SEQUENCE tech_seq_name  INCREMENT BY 1;

How to Reset a Sequence in Oracle

There are many ways.

a) We can drop and recreate the sequence. But this invalidate all dependent objects (triggers/stored procedures etc.)

b) We can reset  by these simple steps

Step 1:Find the latest value of the sequence

Select tech_seq_name.nextval FROM dual;

Step 2: alter the sequence with increment of the negative value of  the latest value

ALTER SEQUENCE tech_seq_name INCREMENT BY -< latest value>  minvalue 0;

Step 3  Do nextval to set it back to zero

SELECT tech_seq_name.nextval FROM dual;

Step 4: Change increment back to 1

ALTER SEQUENCE tech_seq_name  INCREMENT BY 1;

Example

SELECT tech_seq_name.nextval FROM dual;

—————————————

250

ALTER SEQUENCE tech_seq_name INCREMENT BY -250;

SELECT tech_seq_name.nextval FROM dual;

ALTER SEQUENCE tech_seq_name  INCREMENT BY 1;

You can find more on this link

How to reset the sequence

Impact of caching the sequences

Sequence are cached with the purpose of improving the fetch performance. In RAC,each instance stores the cache values

We can have gaps in sequence when using cache due to following reasons

  1. Rollback occurs
  2. System crash or instance crash
  3. Sequence is used in another table

There is new feature in Oracle 12c for sequences

Session sequencesWith Oracle Database 12Cm new keywords SESSION, GLOBAL are available that can be specified during a sequence creation
CREATE SEQUENCE test_session_seq START WITH 1 INCREMENT BY 1 SESSION;
CREATE SEQUENCE test_global_seq START WITH 1 INCREMENT BY 1 GLOBAL;

Global Session
creates standard sequence well known in previous release. This is the default. creates new type session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. Session sequence returns a unique range of sequence numbers only within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.