Synonym

Synonym

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.

Synonym
Scroll to top