Oracle/PLSQL: Synonyms

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 package

view materialized view

sequence java class schema
object

stored user-defined object
procedure

function 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.