Ø A synonym is an
alternative name for objects such as tables, views, sequences, stored
procedures, and other database objects.
Ø By using a synonym, we can avoid the
entry of the schema name, when referencing upon objects that belongs to other
schema.
Ø The create synonym privilege is
necessary to execute the creation of a synonym.
Creating or replacing a synonym
The
syntax for creating a synonym is:
·
create
[or replace] [public] synonym [schema .] synonym_name
For [schema.] object_name;
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.
List synonyms in the current schema:
·
SELECT
synonym_name, table_owner, table_name FROM user_synonyms;