Oracle Faq

Sequence is an oracle database objects which can be used as a counter, or to generate unique identities for a particular field in the table.Sequences are integral part of oracle database and is useful in development of applications

Sequence can be generated in the database and the revelant parameters can be specified while generating as shown

SQL> create sequence <sequence_name>

start with 1

increment by 1

minvalue 0

maxvalue 9999999999999999

nocycle 

cache 20    

order;

the parameters are expalined below:

start with <integer> : The counting with start with integer specified here.The integer can be +ve or -ve

increment by <integer>: The steps of increment/decrement is decided by integer specified here.The integer can be -ve for decrementing

minvalue <integer>: specifies the minvalue the sequence should go when decrementing

maxvalue <integer>: speficies the maxvalue the sequence can go when incrementing

nocycle/cycle : specifies to start again when maxvalue/minvalue is reached

cache <integer>: the no. of integer to be cached

order: The sequence should the output in order when queries .Usefule in RAC systems when the same sequence is queried by 2 different nodes.So order is given the sequence will provide with consistent values

The current value of the sequence is queried as follows:

SQL> select <sequence_name>.currval from dual;

The sequence value can be incremented as:

SQL> select <sequence_name>.nextval from dual;

The currval usually takes the value from nextval.i.e when nextval is executed the value is stored in the currval.therefore when the first time after creating the sequence when you query currval you may get following error

ORA 8002 .CURRVAL is not yet defined in this session

however when u do the nextval first and then currvalue u will not get the above error.

Also u can also any of the above parameters from create sequence by using alter sequence command

alter sequence <sequence_name> increment by 4;

however it is not possible to change the start value for sequence using alter sequence command.Attempting to change the start value will give the following error

ORA 2283 cannot alter starting sequence number

the only option is to drop the sequence and recreate the sequence with required start with

SQL> drop sequence <sequence_name>;

Thanks and Regards

Parikshit