Create a sequence in Oracle with clear explanation

Introduction to Create a Sequence in Oracle

As we discussed the schema objects in the previous session, so in this session that we are going to see one of the schema objects.

This is very important topic that most of the interview questions are comes from here.

At the end of this session, you will be very clear about the below topics related to the sequence:

What is Sequence in Oracle ? Importance of creating sequence in Oracle Syntax for creating sequence in Oracle Data Dictionary for sequences. How to reset the sequence in Oracle ? How to alter the sequence in Oracle ? How to drop the sequence in Oracle ?

NOTE: Answer the Interview Questions at the end of the article

What is Sequence in Oracle ?

Sequence is a numeric value generator which is used to generate the numbers in a sequential order by the incremental order. It is mostly used to generate the primary key value.

Importance of creating sequence in Oracle

It is a schema Objects and it is also a sharable objects Can able to create the sequential number of maximum value up to : 9999..999 (27times of “9”) With the help of this sequence,  we are able to create a unique value for each and every record.

Syntax for creating a sequence in oracle

CREATE SEQUENCE sequence_name

START WITH 

MINVALUE

MAXVALUE

INCREMENT BY 

NOCYCLE

NOCACHE

How to run a sequence in oracle?

Sequence can be run in a select command or else can be run using INSERT statement command, which is in DML.

INSERTING the SEQUENCE

NEXTVAL and CURRVAL

Now, we are going to create a sequence for item and product code.

Item Number Sequence	Product code Sequence
CREATE SEQUENCE item_number_seq START WITH 100 MINVALUE 1 MAXVALUE 120 INCREMENT BY 5 NOCYCLE NOCACHE;	CREATE SEQUENCE prod_code_seq START WITH 1 MINVALUE 1 MAXVALUE 20 INCREMENT BY 1 NOCYCLE NOCACHE;

CREATE SEQUENCE contains the sequence name (_seq is optional, for identifying we are using this naming standard)

START WITH - It is used to start the first sequence from this number.

MINVALUE - When the Cycle completes MAXVALUE it will not moves to the START WITH value, it will moves to the MINVALUE only, this will takes place when we creating a sequence using CYCLE, instead of NOCYCLE.

MAXVALUE - It is the last point where the sequence ends if there is NOCYCLE , and some cases if it is in CYCLE condition means after this MAXVALUE point the sequence will generate the MINVALUE and again the cycle continuous.

INCREMENT BY - It is the incremental number that can give sequential number which are evenly distributed. For example: if it is increment by 2 means , it will be 2, 4, 6, 8, 10.......

CYCLE	NOCYCLE
Generally while creating a sequence it will always start from the START WITH keyword value. After the sequence reaches the MAXVALUE it will again generate the next value as MINVALUE.	During NOCYCLE, the sequence reaches the MAXVALUE means it will end the sequence, it won’t move to the MINVALUE as the NEXT VALUE.

CACHE	NOCACHE
CACHE is used for better performance, because it will store some value in SGA if we mentioned else it will take default value as 20.	NOCACHE it won’t store any value in SGA
It will directly fetch all the CACHED value from the SGA (Shared Global Area), it was not generated by the objects during running time	It will give the next value, which is generated by the sequence objects.

For clear Understanding about CACHE and NOCACHE

Now Sequence is Created in the name of (id_seq), and cache is given as 20. So the sequence one is generated by sequence objects, and the next 20 values will be generated by SGA memory, not by sequence.

The next value after generating the sequence will be checked by using the SELECT statement.

Now flushing the shared pool memory, so as we allocated 20 values in this memory while we created, now it gets flushed.

Now, 20 records which are cached in SGA are flushed so the next value will be taken from the sequence only not by SGA. So after one record this will be printed.

Data Dictionary used in sequences

User_sequences : It is used to check the sequences are present or not, with the help of this we can able to check the Maximum value of running sequence in the LAST_NUMBER column.

How to reset the sequences in oracle ?

To reset a sequence by using a alter command, it will works in latest version of Oracle.

ALTER SEQUENCE sequence_name RESTART START WITH 1;

How to alter the sequences in oracle ?

we can able to alter the sequence in oracle such as reset the sequence, increment the sequence value and altering the MINVALUE and MAXVALUE.

How to drop the sequences in oracle ?

Using DROP keyword we can drop this sequences.

DROP sequence sequence_name;

SQL> DROP sequence id_seq;

Sequence dropped.

CONCLUSION

I hope the after learning this complete article, now you can able to create, alter and run a sequence in oracle database.

If there is any doubts related to the article means, kindly comment, or contact us in official mail.

INTERVIEW QUESTIONS

1, What is sequence in oracle and its data dictionary table used ?

2, Have you used sequence in your project ?

3, How to create a Negative Sequences ?

4, Syntax for creating a sequence ?

5, Difference between CYCLE and NOCYCLE ?

6, Difference between CACHE and NOCACHE ?

7, What is the maximum value limit can be given for sequences ?

8, How to reset a sequence in oracle and also alter commands uses in sequence ?

9, How to use a sequence in oracle ?

10, How to drop a sequence in oracle ?