A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name for [schema .] object_name [@ dblink];
The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.
The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:
- table
- view
- sequence
- stored procedure
- function
- package
- materialized view
- java class schema object
- user-defined object
- synonym
For Example
create public synonym suppliers for app.suppliers;
This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:
select * from suppliers;
If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:
create or replace public synonym suppliers for app.suppliers;
Dropping a synonym
It is also possible to drop a synonym. The syntax for dropping a synonym is:
drop [public] synonym [schema .] synonym_name [force];
The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.
The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.
For Example
drop public synonym suppliers;
This drop statement would drop the synonym called suppliers that we defined earlier.
