In Oracle PL/SQL, a synonym is a database object that allows you to refer to a table, view, sequence, procedure, or another synonym by a different name. Synonyms can be useful in situations where you want to hide the actual name of an object from the user, or to provide a more meaningful name for an object.
Synonyms are created so that applications can refer to tables, views, sequences, and stored procedures without having to know where they are located. A synonym is an alternative name for an object such as a table, view, sequence, or stored procedure.
Synonyms can be public or private. A public synonym is available to all users, while a private synonym is only available to the user who created it. Synonyms are created with the CREATE SYNONYM statement.
Syntax
The syntax for the CREATE SYNONYM statement is:
CREATE OR REPLACE SYNONYM synonym_name FOR object_name;
Where synonym_name is the name of the synonym and object_name is the name of the object that the synonym will represent.
The syntax for the CREATE OR REPLACE PRIVATE SYNONYM statement is:
CREATE OR REPLACE PRIVATE SYNONYM synonym_name FOR object_name;
The CREATE OR REPLACE SYNONYM statement can be used to create or replace a synonym. If a synonym with the specified name already exists, it will be replaced.
Example
For example, suppose you have a table named “EMPLOYEES” in a schema called “HR”, and you want to create a synonym for it so that users can refer to it as “STAFF”. Here’s how you would create the synonym:
CREATE SYNONYM STAFF FOR HR.EMPLOYEES;
Once the synonym is created, users can query the “STAFF” table just as they would query the “EMPLOYEES” table:
SELECT * FROM STAFF;
This would return the same result as:
SELECT * FROM HR.EMPLOYEES;
If you drop an object that a synonym represents, the synonym will still exist but it will no longer be valid.
You can drop a synonym with the DROP SYNONYM statement. The syntax for the DROP SYNONYM statement is:
DROP SYNONYM synonym_name;
You can query the data dictionary to get information about synonyms. To do this, you can query the ALL_SYNONYMS and DBA_SYNONYMS data dictionary views.
The ALL_SYNONYMS data dictionary view contains information about all of the synonyms that are accessible to the current user.
The DBA_SYNONYMS data dictionary view contains information about all of the synonyms in the database.
It’s important to note that the synonym is just a reference to the original object, and any changes made to the original object will be reflected in the synonym and vice versa. If the original object is dropped, the synonym will become invalid.