Synonyms
- 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.