synonym

In Oracle, a synonym is an alias (alternate name) for a database object such as a table, view, sequence, procedure, function, package, or materialized view. Synonyms simplify object access and hide the actual owner of the object.

Types of Synonyms

1. Private Synonym
   • Owned by a specific user.
   • Accessible only by that user (unless privileges are granted).
   Syntax: CREATE SYNONYM emp FOR hr.employees; Usage: SELECT * FROM emp;
2. Public Synonym
   • Available to all users in the database.
   • Requires DBA privileges to create.
   Syntax: CREATE PUBLIC SYNONYM emp FOR hr.employees;

Advantages

• Simplifies SQL statements by avoiding schema names.
• Provides location transparency if objects are moved.
• Helps hide the actual schema owner.
• Makes applications easier to maintain.

Dropping a Synonym

Drop a private synonym:

DROP SYNONYM emp;

Drop a public synonym:

DROP PUBLIC SYNONYM emp;

Example

Without a synonym:

SELECT * FROM hr.employees;

With a synonym:

CREATE SYNONYM employees FOR hr.employees;

SELECT * FROM employees;

This makes queries shorter and independent of the schema name.