Oracle Synonym

In Oracle PLSQL Synonym is an alternative name for objects such as tables, views, sequences, stored procedures and other database objects.

Typically, you use synonyms when you provide access to an object from another schema, and you don’t want users to worry about which schema the object belongs to.

Create (or Replace) Synonym

You can create a synonym so that users do not use a table name prefix with the schema name when using a table in a query.

Syntax for creating Oracle/PLSQL synonyms

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

Allows you to recreate a synonym (if it already exists), without having to issue the DROP synonym command.

• PUBLIC – This means that the synonym is public and available to all users. Remember that the user must first have the appropriate privileges for the synonym object.
• schema – The appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
• object_name – The name of the object for which you are creating the synonym. It can be one of the following objects:

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

Let’s consider an example of how to create a synonym in Oracle/PLSQL

CREATE PUBLIC SYNONYM suppliers
FOR app;

This first example of CREATE SYNONYM demonstrates how to create a synonym with the name. Now, users of other schemes can reference the table with suppliers without the prefix of the scheme name and table name.

SELECT *
FROM suppliers;

If this synonym already exists and you want to recreate it, you can use OR REPLACE as follows:

CREATE OR REPLACE PUBLIC SYNONYM suppliers
FOR app.suppliers;

Drop synonym

After the synonym was created in Oracle, you will need to remove it at some point.

drop synonym syntax in Oracle/PLSQL

DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];

• PUBLIC – Allows you to remove the public synonym. If you specified a PUBLIC, you may not need to specify a schema.
• Force – This will force Oracle to remove the synonym, even if it has dependencies. This is probably not a good idea to use force, as it can invalidate Oracle objects.

Let us consider an example of how to remove synonym in Oracle/PLSQL

For example:

DROP PUBLIC SYNONYM;

The DROP operator will delete the synonym with the name of suppliers that we defined earlier.

Oracle – SQL – Creating Synonyms