Thursday, October 18, 2012

Synonyms Overview

          Synonym is a database object, which represents physical representation of data. Synonym is a alternate name (or) a duplicate name for the original base table.

  • Once we create a synonym, the synonym will be created on the entire base table. It is not possible to create a synonym based on a particular table (or) subset of the table.
  • We can create a synonym with a base table (or) without a base table.
  • We can’t create a synonym based on the more than one table.
  • Once we drop the base table, the corresponding synonym will not be dropped, it becomes invalid. On invalid synonym we can’t perform any type of operations.

The synonym will be invalid in two cases.
        1)when we drop the base table
        2)when we change the table name

Synonyms are classified into two types.

  • Private Synonym
  • Public Synonym

Private Synonym:
           A Synonym which is accessed by or which is confined to one particular user those synonyms can be called as private synonym.
The synonyms created by the normal user.

Public Synonym:
             Public synonyms are the synonyms which are accessed by more than one user simultaneously. These synonyms are generally created by database administrator.

Syntax to create private synonym:
Syntax   : Create synonym <synonym_name>  for <table_name>;
Example: create synonym s for emp;

NOTE: On invalid synonym(if emp table is dropped), if we perform any operation then we are getting the following error message.

Example : select * from s;
ERROR: Synonym translation is no longer valid.

Syntax to create public synonym:
Syntax  : create public synonym 
<synonym_name> for <table_name>;
Example:create public synonym s1 for javanib;

Syntax to see the list of synonyms:
Syntax:select * from user_synonyms;

Syntax to drop Synonym:
Syntax   : drop synonym <synonym_name>;
Example: drop synonym s1;

No comments:

Post a Comment

back to top