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

SQL syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema1.]synonym FOR [schema2.]object

Parameters

The CREATE SYNONYM statement has the parameters:

Parameter	Description
`[OR REPLACE]`	Specify OR REPLACE to re-create 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.
`[schema1.]synonym`	Specify the schema to contain the synonym. If you omit `schema1`, then TimesTen creates the synonym in your own schema. You cannot specify a schema for the synonym if you have specified PUBLIC. `synonym` is the name of the synonym. The name is limited to 30 bytes.
`[schema2.]object`	Specify the schema in which the object resides. `object` is the name of the object for which you are creating a synonym. If you do not qualify `object` with `schema2`, then the database assumes that the schema object is in your own schema.

Description

The schema object does not need to exist when its synonym is created.
You do not need to have privileges to access an object for which you create a synonym.
A private synonym cannot have the same name as an object name 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 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.

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) INLINE1 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.