Oracle SQL: the Essential Reference

CREATE/DROP SYNONYM

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

Creates a public or private synonym for a database object.

DROP [PUBLIC] SYNONYM [schema.]synonym_name

Removes a public or private synonym from the database.

Keywords

PUBLIC

Specifies that this synonym will be available to all users. If omitted, the synonym will be available only to the schema owner.

synonym_name

Specifies the name of the new synonym.

object_name

Specifies the name of the object to which the synonym will refer. It may include a reference to a remote database by appending the @dblink syntax.

Oracle resolves object names in the current schema first, so a PUBLIC synonym will only be used if the object name is not prefaced with a schema name, is not followed by a dblink, and does not exist in the current schema.

Examples

Create a public synonym for scott's emp table on the UK database:

CREATE PUBLIC SYNONYM uk_emps
FOR scott.emp@UK;

Remove the public synonym uk_emps from the database:

DROP PUBLIC SYNONYM uk_emps;