SYNONYM: (04-12-2024)
==========
- it is a database object which is used to create permanent alias name for
table name
(or) object name in database.
Purpose of synonyms:
===================
1. to reduce lengthy table names.
2. to hide owner name and table name from the users.
Types of synonyms:
=================
- oracle supports the following two types of synonyms.
1. Private synonym
2. Public synonym
1. Private synonym:
================
- these synonyms are created by users who are having permission.
syntax:
======
create synonym <synonym name> for [owner name].<table name>;
EX:
SQL> CONN
Enter user-name: SYSTEM/TIGER
Connected.
SQL> CREATE USER U11 IDENTIFIED BY U11;
User created.
SQL> GRANT CONNECT,CREATE TABLE,UNLIMITED TABLESPACE TO U11;
Grant succeeded.
SQL> CONN
Enter user-name: U11/U11
Connected.
SQL> CREATE TABLE COLLEGE_ENROLLMENT_DETAILS(STID NUMBER(4),SNAME
VARCHAR2(10),BRANCH VARCHAR2(10));
Table created.
Granting "create synonym" permission to user:
=======================================
SQL> CONN
Enter user-name: SYSTEM/TIGER
Connected.
SQL> GRANT CREATE SYNONYM TO U11;
SQL> CONN
Enter user-name: U11/U11
Connected.
SQL> CREATE SYNONYM CED FOR COLLEGE_ENROLLMENT_DETAILS.
synonym created.
TESTING:
SQL> SELECT * FROM CED;
SQL> INSERT INTO CED VALUES(1021,'SMITH','CSE');
SQL> UPDATE CED SET BRANCH='EEE' WHERE STID=1021;
SQL> DELETE FROM CED WHERE STID=1021;
- In the above operations we are used synonym name(CED) instead of
lengthy table name(COLLEGE_ENROLLMENT_DETAILS).
NOTE:
=====
- to view synonym name along with table name in oracle database then we use a
datadictionary
is "user_synonyms".
EX:
SQL> DESC USER_SYNONYMS;
SQL> SELECT SYNONYM_NAME,TABLE_NAME,TABLE_OWNER FROM USER_SYNONYMS;
SYNONYM_NAME TABLE_NAME TABLE_OWNER
---------------------------- ----------------------- ------------
-----------------------------------------------------------------
CED COLLEGE_ENROLLMENT_DETAILS U11
How to drop a private synonym:
==========================
syntax:
=======
DROP SYNONYM <SYNONYM NAME>;
EX:
SQL> DROP SYNONYM CED;
2) Public synonym:
=================
- these synonyms are created by DBA for hiding owner name and also table name
from users.
syntax:
======
create public synonym <synonym name> for [owner name].<table name>;
EX:
SQL> CONN
Enter user-name: SYSTEM/TIGER
Connected.
SQL> CREATE PUBLIC SYNONYM PS1 FOR [Link];
Synonym created.
SQL> GRANT SELECT ON PS1 TO U11;
Grant succeeded.
SQL> CONN
Enter user-name: U11/U11
Connected.
SQL> SELECT * FROM PS1;
NOTE:
=====
- to view public synonym along with table name in oracle then use a
datadictionary
is "all_synonyms".
EX:
SQL> DESC ALL_SYNONYMS;
SQL> SELECT SYNONYM_NAME,TABLE_NAME,TABLE_OWNER,OWNER
2 FROM ALL_SYNONYMS WHERE TABLE_NAME='DEPT';
SYNONYM_NAME TABLE_NAME TABLE_OWNER OWNER
---------------------------- ----------------------- --------------------------
---------------------------------------------------
PS1 DEPT SYSTEM PUBLIC
How to drop public synonym:
========================
syntax:
=======
DROP PUBLIC SYNONYM <SYNONYM NAME>;
EX:
SQL> DROP PUBLIC SYNONYM PS1;
===================================================================================
=============
SEQUENCE: (05-12-2024)
==========
- it is a db object which is used for generating the sequence numbers on a
specific column
in the table automatically.
- it will provide "auto incremental value" facility.
syntax:
======
create sequence <sequence name>
[start with n]
[minvalue n]
[increment by n]
[maxvalue n]
[no cycle / cycle]
[no cache / cache n];
start with n:
==========
- to specify starting value of [Link] "n" is a number.
minvalue n:
=========
- to show minimum value in the [Link] "n" is a number.
increment by n:
=============
- fo specify incremental value in between seqeucne [Link] "n" is a
number.
maxvalue n:
==========
- to show maximum value from [Link] "n" is a number.
no cycle:
========
- it a default attribute of sequence object.
- when we created a sequecne object with "NO CYCLE" then the set of sequence
numbers are not repeat again and again.
cycle:
========
- when we created a sequence object with "CYCLE" then the set of sequence
numbers are repeat again and again.
no cache:
=========
- it is a default attribute of sequence object.
- cache is a temporary memory.
- when we created a sequence object with "NO CACHE" then the set of sequence
numbers are saved in database memory [Link] that each and every user request
will go to
database and retrieving the required data from database and send to client
application.
by this reason the database burdon will increse and degrade the performance of
database.
cache n:
=======
- when we created a sequence object with "CACHE" then the set of sequence
numbers are saved in database memory and also the copy of data is saved in cache
memory.
so that now every user request will go to cache instead of database and retrieving
the required data
from cache memory and send to client [Link] that we reduce the burdon on
database
and improve the performance of database.
NOTE:
======
- when we want generate sequence numbers on a column then we must use a
pseudo column
of sequence object is "NEXTVAL".
NEXTVAL:
=========
- it is used to generate next by next number.
syntax:
======
<sequence name>.<column name>
EX1:
SQL> CREATE SEQUENCE SQ1
2 START WITH 1
3 MINVALUE 1
4 INCREMENT BY 1
5 MAXVALUE 3;
Sequence created.
TESTING:
=========
SQL> CREATE TABLE TEST50(SNO NUMBER(4),NAME VARCHAR2(10));
SQL> INSERT INTO TEST50 VALUES([Link],'&NAME');
Enter value for name: A
SQL> /
Enter value for name: B
SQL> /
Enter value for name: C
SQL> /
Enter value for name: D
ERROR at line 1:
ORA-08004: sequence [Link] exceeds MAXVALUE and cannot be instantiated
ALTERING A SEQUENCE:
======================
syntax:
=======
alter sequence <sequence name> <attribute name> n;
EX:
SQL> ALTER SEQUENCE SQ1 MAXVALUE 5;
Sequence altered.
SQL> INSERT INTO TEST50 VALUES([Link],'&NAME');
Enter value for name: D
SQL> /
Enter value for name: E
OUTPUT:
=========
SQL> SELECT * FROM TEST50;
SNO NAME
---------- ----------
1 A
2 B
3 C
4 D
5 E
EX2:
SQL> CREATE SEQUENCE SQ2
2 START WITH 3
3 MINVALUE 1
4 INCREMENT BY 1
5 MAXVALUE 5
6 CYCLE
7 CACHE 2;
Sequence created.
TESTING:
SQL> CREATE TABLE TEST51(SNO NUMBER(3),NAME VARCHAR2(10));
SQL> INSERT INTO TEST51 VALUES([Link],'&NAME');
Enter value for name: A
SQL> /
Enter value for name: B
SQL> /
.......................................
OUTPUT:
========
SQL> SELECT * FROM TEST51;
NOTE:
======
- To view all sequences objects in oracle database then we use a
datadictionary is "user_sequences".
EX:
SQL> DESC USER_SEQUENCES;
SQL> SELECT SEQUENCE_NAME FROM USER_SEQUENCES;
How to drop a sequecne object:
===========================
syntax:
======
DROP SEQUENCE <SEQUENCE NAME>;
EX:
SQL> DROP SEQUENCE SQ1;
