Oracle Techniques By Sameer Wadhwa (Sequence Numbers)

SAMEER WADHWA

DROPPING SEQUENCE NUMBER

REASON FOR MISSING SEQUENCE NUMBERS

Syntax

Create Sequence <name>

Increment by <n>

Start with <n>

Maxvalue <n>

Cycle/nocycle

Cache <n>

Where

Ø Increment	Refers to the increment value
Ø Start with	The starting value, default value is 1
Ø Maxvalue	The highest value of the sequence for ascending.
Ø Minvalue	The lowest value of the sequence for descending
Ø Cycle	Recycle the sequence number after reaching max value
Ø Nocycle	On reaching max value it will give an error
Ø Cache	Preallocate sequence number in memory . The default value of cache is 20

Q. How do I create an Incremental Sequence ?

A. To create an incremental sequence , you have to define INCREMENT BY clause with positive number . This positive number should be an incremental number. So if we want to create a sequence which increases by 2 every time , we have to define as follows :-

CREATE SEQUENCE myseq

INCREMENT BY 2

START WITH 5

MAXVALE 30

CYCLE

CACHE 10;

Here the sequence myseq is created , the starting number of the sequence is 5 , each sequence number will be increased by 2. i.e the first number would be 5, second would be 7, third would be 9 and so on. The sequence will go up to 30 and after that it again start with 5. Defining nocycle , instead of cycle will raise an error , once sequence will get the max value.

Cache pre-allocates sequence number in memory . This should be less than total sequence number generated values . In the above example defining cache as 20 will raise ORA-04013 because between 5 and 30 there is a chance to generate a 13 sequence numbers. Cache value should be less than the total expected generated numbers.

Q. How do I create a decreasing Seqeunce ?

A. To create an decreasing sequence , you have to define INCREMENT BY clause with negative number . This negative number should be a decreasing number. So if we want to create a sequence which decreases by 2 every time , we have to define as as follows :-

CREATE SEQUENCE myseq

INCREMENT BY -2

START WITH 30

MINVALUE 0

MAXVALUE 30

CYCLE

CACHE 10

Here the sequence myseq is having starting number 30 ,since it is decreasing sequence its maxvalue will also be 30. Each time sequence number will be decrease by 2. The first number would be 30, second would be 28 and so on.. The sequence number will go to zero and again start with 30 since we are using cycle.

Q. How do I know the current value and next value of my Sequence ?

A.There are two pseudocolumns CURRVAL and NEXTVAL used with sequence.

Select myseq.nextval from dual will tell you the next value of your sequence.

Select myseq.currval from dual will tell you the current value of your sequence.

When the sequence is created , First time you have to execute NEXTVAL before CURRVAL , otherwise you will get an Oracle error ORA-08002 . By executing NEXTVAL , oracle puts the pointer to the new value.

Q. How do I insert a sequence number in to the table ?

A. Let us consider table mytab

(increment_number number,

description varchar2(40));

and suppose we want to insert increment number through sequence , To do that give following insert statement as

Insert into mytab(myseq.nextval,’ NEXT VALUE OF SEQUENCE VALUE INSERTED’);

Commit;

MYSEQ.NEXTVAL will generate a next sequence number to be inserted into the table.

Q. What Data Dictionary views give me info about sequences ?

A.The Data dictionary views are ALL_SEQUENCES,USER_SEQUENCES,DBA_SEQUENCES gives us the info about generated sequences.

SQL> desc user_Sequences;

----------------------------------------- -------- ----------------------------

SEQUENCE_NAME NOT NULL VARCHAR2(30)

MIN_VALUE NUMBER

MAX_VALUE NUMBER

INCREMENT_BY NOT NULL NUMBER

CYCLE_FLAG VARCHAR2(1)

ORDER_FLAG VARCHAR2(1)

CACHE_SIZE NOT NULL NUMBER

LAST_NUMBER NOT NULL NUMBER

As sequence created in Question 2 , the detail of it can be seen by user_sequences as follows :-.

SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

--------------- ---------- ---------- ------------ - - ----------- -----------

MYSEQ 0 30 -2 Y N 10 30

Q. I tried to create a sequence in my schema but I get an insufficient privileges error . What privileges do I need to create a sequence ?

A.To create a sequence in your schema , you must have CREATE SEQUENCE privilege.

To create a sequence in another user’s schema, you need CREATE ANY SEQUENCE privilege.

To alter a sequence in another user’s schema , you need ALTER ANY SEQUENCE privilege.

Q. Is there any restriction on NEXTVAL and CURRVAL ?

A. There are certain restriction on NEXTVAL and CURRVAL as follows :-

1) NEXTVAL and CURRVAL can not be used with select statement with distinct operator.

2) It can not be used with select statement having group by or order by clause.

3) It can not be used with view query and snapshot query

Q. Can I create a Sequnce through database procedure ?

A.You can create a stored procedure for creation of sequence

create or replace procedure myseq_proc

CursorSeq integer;

SeqString varchar2(200);

begin

CursorSeq := dbms_sql.open_cursor;

SeqString := ' create sequence myseq'||

' increment by 2'||

' start with 5'||

' maxvalue 30'||

' cycle'||

' cache 10';

dbms_sql.parse(CursorSeq,SeqString,Dbms_sql.native);

dbms_sql.close_cursor(CursorSeq);

end;

You have to execute mysql_proc procedure to create a sequence myseq.

For an Oracle Version 8.1.x onwards you can modify above procedure as follows

create or replace procedure myseq_proc

SeqString varchar2(200);

begin

SeqString := ' create sequence myseq'||

' increment by 2'||

' start with 5'||

' maxvalue 30'||

' cycle'||

' cache 10';

execute immediate(SeqString);

end;

Q. I have an enough memory and i want to put my sequence in it. How do i cache a sequence ?

A. To cache a sequence in memory you have to use DBMS_SHARED_POOL package as follows

DBMS_SHARED_POOL.KEEP(‘< SEQUENCE_NAME>’,’Q’);

For exp to cache myseq in memory ,

Execute DBMS_SHARED_POOL.KEEP(‘MYSEQ’,’Q’);

(For detail look at my article on DBMS_SHARED_POOL )

Q. How do I drop a Sequence ?

A. To drop a sequence you must have drop sequence privilege.

Drop sequence < Sequence Name>;

To drop Sequence of another schema, you must have drop any sequence privilege.

Drop Sequence <schema name>.<Sequence Name >;

Q. I found that some of my sequence numbers are missing . How do I avoid this ?

A. Missing a sequence number is a common problem. There are various reasons for it

1) Once the sequence number is generated it can not be rolled back, so if you rollback a transaction which generated some sequence numbers ,that numbers can not be used and become wasted.

2) Same sequence numbers may be using by more than one table concurrently.

3) Cached sequence numbers may be lost by shutdown abort or system crash

---------End of Article --------

Hope this article helped you to understand Sequences in Oracle Database. Please feel free to send me your comments / feedback or suggestions at Wadhwa_S@Hotmail.com

Thanks

Sameer Wadhwa