Synonyms in Oracle SQL PLSQL

Oracle SQL / PLSQL uses synonym as an alias for any database object such as tables, views, sequences, stored procedures, and other database object.

In other words we can say that in Oracle SQL / PLSQL a synonym is an alternative name for database objects.

Oracle SQL / PLSQL syntax to create or replace a synonym is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA.] synonym_name
FOR [SCHEMA.] object_name [@ dblink];

The OR REPLACE phrase in the plsql create synonym syntax above allows us to replace or recreate any plsql synonym (if already existing) for the same database object and without using the drop command.

The PUBLIC phrase in the above plsql create synonym syntax means that the synonym will be accessible for all the valid users, but the user must have the sufficient privileges for the object to use its synonym.

The SCHEMA phrase in the plsql create synonym is the name of the schema where the synonym will reside. It is an optional phrase, if omitted; oracle plsql creates the synonym in the current schema.

The SYNONYM_NAME in the above plsql create synonym syntax is the name of the synonym.

The OBJECT_NAME in the above plsql create synonym syntax is the name of the database object for which the synonym is to be created.

The object can be any of the following:

1. Table
2. View
3. Sequence
4. Stored procedure
5. Function
6. Package
7. Materialized view
8. Java class schema object
9. User-defined object
10. Synonym

Example to create a synonym for ‘employee’ table

CREATE PUBLIC SYNONYM employee_syn
FOR employee;

Once we run the above PLSQL CREATE SYNONYM command we can fetch the records of the employee table as:

SELECT *
FROM employee_syn;

If the ‘employee_syn’ synonym already existed we can replace the synonym using the CREATE OR REPLACE SYNONYM as:

CREATE OR REPLACE PUBLIC SYNONYM employee_syn
FOR employee;

Example to drop a plsql synonym:

Oracle SQL / PLSQL allow us to drop a synonym that we have created earlier.

The syntax to drop a plsql synonym is:

DROP [PUBLIC] SYNONYM [SCHEMA.] synonym_name [FORCE];

The PUBLIC phrase in the above oracle plsql drop synonym syntax allows us to drop a public synonym, if we have specified public then we don’t have to specify a schema for the plsql synonym.

The FORCE phrase in the above oracle plsql drop synonym syntax will drop the synonym even if there are dependencies on it.

Example to drop a plsql synonym is:

DROP PUBLIC SYNONYM employee_syn;

The above plsql drop synonym command will drop the synonym ‘employee_syn’ from the database.