SYNONYM AND SEQUENCE

SYNONYM

A synonym is a database object, which is used as an alias for a table, view or sequence.

TYPES

1.   Private

2.     Public

Private synonym is available to the particular user who creates.

Public synonym is created by DBA which is available to all the users.

ADVANTAGES

1     Hide the name and owner of the object.

2     Provides location transparency for remote objects of a distributed database.

CREATE AND DROP

SQL> create synonym s1 for emp;

SQL> create public synonym s2 for emp;

SQL> drop synonym s1;

SEQUENCE

A sequence is a database object, which can generate unique, sequential integer values.

It can be used to automatically generate primary key or unique key values.

A sequence can be either in an ascending or descending order.

Syntax:

      Create sequence <seq_name> [increment bty n] [start with n] [maxvalue n]

                                  [minvalue n] [cycle/nocycle] [cache/nocache];

By defalult the sequence starts with 1, increments by 1 with minvalue of 1 and with nocycle,  nocache.

Cache option pre-alloocates a set of sequence numbers and retains them in memory for faster access.

Ex:

     SQL> create sequence s;

     SQL> create sequence s increment by 10 start with 100 minvalue 5 maxvalue 200 cycle  

             cache 20;

USING SEQUENCE

SQL> create table student(no number(2),name varchar(10));

SQL> insert into student values(s.nextval, ‘saketh’);

1     Initially currval is not defined and nextval is starting value.

2     After that nextval and currval are always equal.

CREATING ALPHA-NUMERIC SEQUENCE

SQL> create sequence s start with 111234;

SQL> Insert into student values (s.nextval || translate  

         (s.nextval,’1234567890’,’abcdefghij’));

ALTERING SEQUENCE

We can alter the sequence to perform the following.

1     Set or eliminate minvalue or maxvalue.

2     Change the increment value.

3     Change the number of cached sequence numbers.

Ex:

     SQL> alter sequence s minvalue 5;

     SQL> alter sequence s increment by 2;

     SQL> alter sequence s cache 10;

DROPPING SEQUENCE

SQL> drop sequence s;