Sequences and synonyms

Sequence: Sequence is a type of object that oracle supports, which can be used to generate numbers in sequence order, this can be used to generate numbers for primary keys.

Syntax:

SQL> Create sequence <sequence_name> start with <integer_value> increment by <integer_values> maxvalue <integer_value> minvalue <integer_value> nocycle or cycle nocache or cache or order;

Start with: First sequence number to be generated.

Increment by: The integer value by which sequence number should be incremented for generating next number.

Maxvalue: If the increment number is positive then maxvalue determines the maxvalue up to which the sequence numbers will be generated.

NOMaxvalue: specifies a maximum value of 10^27 for an ascending sequence or -1 for a descending sequence.

Minvalue: If the increment number is negative then minvalue determines the minvalue up to which the sequence numbers will be generated.

NOMinvalue: specifies a minvalue of 1 for an ascending sequence and 10^26 for a descending sequence.

Cycle: Causes the sequence to automatically recycle to minvalue is reached for a ascending sequence, for descending sequences, it causes a recycle from minvalue back to maxvalue.

NOCycle: Sequence numbers willnot be generated after reaching the maxvalue for ascending sequences or minimum value for descending sequences.

Cache: Specifies how many values are pre-allocated in buffer for faster access. Default value is 20.

NOCache: Sequence numbers are not pre-allocated.

Order: Generates the number in serial order.

NOOrder: Generates the numbers in a random order.

SQL> Create Sequence sq_no start with 1000 increment by 1 maxvalue 9999 nocycle nocache order.

SQL> Insert into emp values(sq_no.nextval,’&empname’); // inserting using sequnce

Modifying Sequence:

SQL> Alter sequence sq_emp increment by 2; // alter sequence by 2 numbers

SQL> Select sq_emp.currval from dual; // current sequence number

SQL> Select sq_emp.next from dual;//next generating sequence number

SQL> Drop sequence sq-emp; // dropping a sequence

Synonyms: It is an alternate name to an object.

SQL> Create Synonym employee for emp; // synonym created

SQL> Drop synonym employee; //synonym dropped

Data Dictionaty:

SQL> Select * from user_sysnonyms; // shows user created synonyms

SQL> Select * from user_sequences;// shows user created sequences