CREATE SYNONYM

The CREATE SYNONYM statement creates a public or private synonym for a database object. A synonym is an alias for a database object. The object can be a table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view or cache group.

A private synonym is owned by a specific user and exists in that user's schema. A private synonym is accessible to users other than the owner only if those users have appropriate privileges on the underlying object and specify the schema along with the synonym name.

A public synonym is accessible to all users as long as the user has appropriate privileges on the underlying object.

CREATE SYNONYM is a DDL statement.

Synonyms can be used in these SQL statements:

DML statements: SELECT, DELETE, INSERT, UPDATE, MERGE
Some DDL statements: GRANT, REVOKE, CREATE TABLE ... AS SELECT, CREATE VIEW ... AS SELECT, CREATE INDEX, DROP INDEX
Some cache group statements: LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP, FLUSH CACHE GROUP

Required Privilege

CREATE SYNONYM (if owner) or CREATE ANY SYNONYM (if not owner) to create a private synonym.

CREATE PUBLIC SYNONYM to create a public synonym.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM [Owner1.]synonym FOR [Owner2.]object

Parameters

Parameter	Description
`[OR REPLACE]`	Specify `OR REPLACE` to recreate the synonym if it already exists. Use this clause to change the definition of an existing synonym without first dropping it.
`[PUBLIC]`	Specify `PUBLIC` to create a public synonym. Public synonyms are accessible to all users, but each user must have appropriate privileges on the underlying object in order to use the synonym. When resolving references to an object, TimesTen uses a public synonym only if the object is not prefaced by a schema name.
`[Owner1.]synonym`	Specify the owner of the synonym. You cannot specify an owner if you have specified `PUBLIC`. If you omit both `PUBLIC` and `Owner1`, TimesTen creates the synonym in your own schema. Specify the name for the synonym, which is limited to 30 bytes.
`[Owner2.]object`	Specify the owner in which the object resides. Specify the object name for which you are creating a synonym. If you do not qualify `object` with `Owner2`, the object is in your own schema. The `Owner2` and `object` do not need to exist when the synonym is created.

Description

The schema object does not need to exist when its synonym is created.
Do not create a public synonym with the same name as a TimesTen built-in procedure.
In order to use the synonym, appropriate privileges must be granted to a user for the object aliased by the synonym before using the synonym.
A private synonym cannot have the same name as tables, views, sequences, PLSQL packages, functions, procedures, and cache groups that are in the same schema as the private synonym.
A public synonym may have the same name as a private synonym or an object name.
If the PassThrough attribute is set so that a query needs to executed in the Oracle database, the query is sent to the Oracle database without any changes. If the query uses a synonym for a table in a cache group, then a synonym with the same name must be defined for the corresponding Oracle database table for the query to be successful.
When an object name is used in the DML and DDL statements in which a synonym can be used, the object name is resolved as follows:
1. Search for a match within the current schema. If no match is found, then:
2. Search for a match with a public synonym name. If no match is found, then:
3. Search for a match in the SYS schema. If no match is found, then:
4. The object does not exist.
TimesTen creates a public synonym for some objects in the SYS schema. The name of the public synonym is the same as the object name. Thus steps 2 and 3 in the object name resolution can be switched without changing the results of the search.
In a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL is 2 or greater when you execute CREATE SYNONYM on the active database, the synonym is replicated to all databases in the replication scheme. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information.

Examples

As user ttuser, create a synonym for the jobs table. Verify that you can retrieve the information using the synonym. Display the contents of the SYS.USER_SYNONYMS system view.

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.

Create a public synonym for the employees table.

Command> CREATE PUBLIC SYNONYM pubemp FOR employees;
Synonym created.

Verify that pubemp is listed as a public synonym in the SYS.ALL_SYNONYMS system view.

Command> SELECT * FROM sys.all_synonyms;
< PUBLIC, TABLES, SYS, TABLES, <NULL> >
...
< TTUSER, SYNJOBS, TTUSER, JOBS, <NULL> >
< PUBLIC, PUBEMP, TTUSER, EMPLOYEES, <NULL> >
57 rows found.

Create a synonym for the tab table in the terry schema. Describe the synonym.

Command> CREATE SYNONYM syntab FOR terry.tab;
Synonym created.
Command> DESCRIBE syntab;
 
Synonym TTUSER.SYNTAB:
  For Table TERRY.TAB
  Columns:
    COL1                            VARCHAR2 (10) INLINE
    COL2                            VARCHAR2 (10) INLINE

1 Synonyms found.

Redefine the synjobs synonym to be an alias for the employees table by using the OR REPLACE clause. Describe synjobs.

Command> CREATE OR REPLACE synjobs FOR employees;
Synonym created.
 
Command> DESCRIBE synjobs;
 
Synonym TTUSER.SYNJOBS:
  For Table TTUSER.EMPLOYEES
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20) INLINE
    LAST_NAME                       VARCHAR2 (25) INLINE NOT NULL
    EMAIL                           VARCHAR2 (25) INLINE UNIQUE NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20) INLINE
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) INLINE NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)
 
1 Synonyms found.