A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and database objects.
CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA .] SYNONYM_NAME FOR [SCHEMA .] OBJECT_NAME [@ DBLINK];
In the syntax,
Ø The OR replace phrase allows 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 a reference is made to the user's 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:
q Stored Procedure
q User Defined Object
Create a synonym to a table named EMP held by the user SCOTT.
CREATE PUBLIC SYNONYM EMPLOYEES FOR SCOTT.EMP;
Now, users of other schemas can reference the table EMP, which is now called as EMPLOYEES without having to prefix the table name with the schema named SCOTT. For example:
SELECT * FROM EMPLOYEES;
DROP [PUBLIC] SYNONYM [SCHEMA .] SYNONYM_NAME [FORCE];
In the syntax,
Ø The PUBLIC phrase allows to drop a public synonym. If public is specified, then there is no need to 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.
Drop the public synonym named EMPLOYEES
DROP PUBLIC SYNONYM EMPLOYEES;