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