Creating Synonyms

Create the synonym with the CREATE SYNONYM statement.

You can use the CREATE OR REPLACE SYNONYM statement to change the definition of an existing synonym without needing to drop it first. The CREATE SYNONYM and CREATE OR REPLACE SYNONYM statements specify the synonym name and the schema name in which the synonym is created. If the schema is omitted, the synonym is created in the user's schema. However, when creating public synonyms, do not provide the schema name as it is defined in the PUBLIC namespace.

In order to issue the CREATE SYNONYM or CREATE OR REPLACE SYNONYM statements, the user must have the appropriate privileges, as described in Object Privileges for Synonyms in the Oracle TimesTen In-Memory Database Security Guide.

  • Object types for synonyms: The CREATE SYNONYM and CREATE OR REPLACE SYNONYM statements define an alias for a particular object, which can be one of the following object types: table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view, or cache group.

    Note:

    If you try to create a synonym for unsupported object types, you may not be able to use the synonym.

  • Naming considerations: A private synonym shares the same namespace as all other object names, such as table names and so on. Therefore, a private synonym cannot have the same name as a table name or other objects in the same schema.

    A public synonym is accessible for all users and does not belong to any particular user schema. Therefore, a public synonym can have the same name as a private synonym name or other object name. However, you cannot create a public synonym that has the same name as any objects in the SYS schema.

In the following example, the user creates a private synonym of synjobs for the jobs table. Issue a SELECT statement on both the jobs table and the synjobs synonym to show that selecting from synjobs is the same as selecting from the jobs table. Finally, to display the private synonym, the example runs a SELECT statement on the SYS.USER_SYNONYMS table. 

Command> CREATE SYNONYM synjobs FOR jobs;
Synonym created.

Command> SELECT FIRST 2 * FROM jobs;
< AC_ACCOUNT, Public Accountant, 4200, 9000 >
< AC_MGR, Accounting Manager, 8200, 16000 >
2 rows found.
Command> SELECT FIRST 2 * FROM synjobs;
< AC_ACCOUNT, Public Accountant, 4200, 9000 >
< AC_MGR, Accounting Manager, 8200, 16000 >
2 rows found.

Command> SELECT * FROM sys.user_synonyms;
< SYNJOBS, TTUSER, JOBS, <NULL> >
1 row found.

See CREATE SYNONYM in the Oracle TimesTen In-Memory Database SQL Reference.