Oracle Database Administration: The Essential Refe

Name

CREATE SYNONYM

Synopsis

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

Creates a public or private synonym (synonym_name) for a database object.

Keywords

PUBLIC

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

FOR 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 @dblink.

Notes

You must have the CREATE SYNONYM privilege to create a private synonym in your own schema, and CREATE ANY SYNONYM to create a synonym in another schema. You must have the CREATE PUBLIC SYNONYM privilege to create a public synonym. Oracle will resolve object names in the schema first, so a public synonym will only be used if the object name is not prefaced with a schema name or followed by @dblink and if the object name does not exist in the current schema.

Example

The following example creates a public synonym for scott’s emp table on the UK database:

CREATE PUBLIC SYNONYM uk_emps
FOR scott.emp@UK