SYNONYM AND SEQUENCE
A synonym is a database object, which is used as an alias for a table, view or sequence.
Private synonym is available to the particular user who creates.
Public synonym is created by DBA which is available to all the users.
- 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;
- 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.
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.
SQL> create sequence s;
SQL> create sequence s increment by 10 start with 100 minvalue 5 maxvalue 200 cycle
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
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.
SQL> alter sequence s minvalue 5;
SQL> alter sequence s increment by 2;
SQL> alter sequence s cache 10;
SQL> drop sequence s;