Create synonyms

You cannot create synonyms for emote databases or other synonyms (also called chaining). Because synonyms share namespace as their objects, you cannot create a synonym with a name that is already used by an object of that type. That is, you cannot create a synonym named “my_tbl” for a table if there is already a table named “my_tbl” in that namespace. Conversely, you cannot create a table, view, or function with a name that matches an existing synonym in that namespace.

All synonyms are public and viewable by all users.

The syntax for the CREATE SYNONYM command is:
CREATE SYNONYM synonym_name FOR table_reference;

The synonym_name is a name that follows the naming conventions for a table, view, and function. You can create a synonym for a non-existent table, view, or function. At run time, the system expands the reference to its fully qualified form. If the referenced object does not exist, the system displays an error message.

The reference can be one of the following names:
  • Plain name (table, view, or function name)
  • Database qualified name (database_name.schema_name.object name)
For example, to create a synonym for EMP in the default schema of database PROD, enter:
dev.sch1(admin)=> CREATE SYNONYM pemp FOR prod..emp;