Tuesday, May 11, 2010

Oracle Synonyms

A synonym is an alias for another database object (table, view, sequence, procedure, function or package in local database).
Public Synonym - Available to all users.
CREATE PUBLIC SYNONYM employees FOR hr.employees;
Used to identify well known objects, e.g. data dictionary views (USER_OBJECTS etc)

Private Synonym - Available to the owner or the account to whom owner gives privileges.
CREATE SYNONYM employees FOR hr.employees;
Are useful when the table is renamed and old and new names are needed.

Synonym don't get invalid if the object to they point is dropped. Synonym can be created for any object which does not exist or the owner does not have privileges.

Removing Synonym :
Public: DROP PUBLIC SYNONYM employees;
Private: DROP SYNONYM employees;

References to an object is looked up in following order:
  • An object owned by current user.
  • A private synonym owned by current user.
  • A public synonym.

No comments:

Post a Comment

Amazon Best Sellors

Weblogic - Server Creation - Issues

Datasource creation using python script.  Python Script was failing after creating Datasource. From UI Admin Console, if I try to activat...