1/28/16

SYNONYM AND SEQUENCE

SYNONYM


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


TYPES

Ø  Private

Ø  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


Ø  Hide the name and owner of the object.

Ø  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’);


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

Ø  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.

Ø  Set or eliminate minvalue or maxvalue.

Ø  Change the increment value.

Ø  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;



0 comments:

Post a Comment