www.bullraider.com

Synonyms

AddThis Social Bookmark Button
  • It is an alias for another database object.
  • It is used to give different names for an individual object
  • A Synonym can be  pointing  to a local database object( table, view) or to an object in another database.
  • Synonyms are also used to shorten lengthy object names.

There are two types of synonym:
PRIVATE synonym
PUBLIC synonym

PUBLIC SYNONYM

  • It is available to all users.
  • Public synonyms don’t require owner name
  •  

PRIVATE SYNONYM

  • It is available to the owner or to the accounts to whom that owner grants privileges.
  • Private synonyms can be created for objects that you own or objects owned by other users.

Example for PUBLIC synonym:
CREATE PUBLIC SYNONYM emp FOR scott.emp;
Example for PRIVATE synonym:
CREATE SYNONYM emp FOR scott.emp

DROP  SYNONYM
Example for PUBLIC synonym:
DROP PUBLIC SYNONYM emp;
Example for PRIVATE synonym:
DROP SYNONYM emp;

Note:

  • To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege.
  • To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege.
  • A private synonym name must be unique in its schema.
  • You cannot specify a schema for the synonym if you have specified PUBLIC.

Powered by Bullraider.com