Recent Posts

Saturday, 9 July 2016

Synonym Tutorial


     Synonym is a database object.  It is a shortcut name for original base table and it contains the physical representation of data.

* If we create physical representation of data synonym will be created on the entire table, and it is not possible to create a synonym based on the partial table and subset of a table.

* We can’t create a synonym more than one table.

* If we perform any DML operation on synonym it automatically shows effect on base table also. Like that if make any modification on base table it automatically modify the synonym.

* If we drop the base table the corresponding synonyms won’t be dropped and those become invalid.

* Synonyms will become invalid in two cases.
1. When we drop the base table.
2. When we change the table name.
* On invalid synonym It is not possible to perform any type operations. We can make the invalid synonym as valid synonym we can create a synonym with a base table without a base table.

* On invalid synonym if the user trying to perform the operations then we will get the following error message.

Synonym translation is no longer valid.
* Synonyms are classified into two types.
1. Private Synonyms
     Private synonyms are synonyms those are confined to only one particular user and these synonyms are access by one particular user.

2. Public synonyms
     Public synonyms are synonyms those are accessed by many users at a time those synonyms can be called as public synonyms. Public synonyms always created by DBA.
Syntax for creating a synonym
CREATE SYNONYM<synonym name> FOR <table name>
E.g
create synonym v for EMP;

Public synonyms
Syntax
create public synonym <synonym name> for <table name>
E.g
create public synonym ps for po;
Syntax to drop particular synonym
Syntax
DROP SYNONYM <Synonym name>
E.g
drop synonym B;
Note
     We can’t modify the structure of the synonym.

Next Tutorial  Views Tutorial

Previous Tutorial  SQL Sub Queries

No comments:

Post a Comment