Understanding Synonyms

A synonym is an alias for a database object. Synonyms are often used for security and convenience, because they can be used to mask object name and object owner.

In addition, you can use a synonym to simplify SQL statements. Synonyms provide independence in that they permit applications to function without modification regardless of which object a synonym refers to. Synonyms can be used in DML statements and some DDL and TimesTen cache statements.

Synonyms are categorized into two classes:

• Private synonyms: A private synonym is owned by a specific user and exists in the schema of a specific user. A private synonym shares the same namespace as other object names, such as table names, view names, sequence names, and so on. Therefore, a private synonym cannot have the same name as a table name or a view name in the same schema.

• Public synonyms: A public synonym is owned by all users and every user in the database can access it. A public synonym is accessible for all users and it does not belong to any user schema. Therefore, a public synonym can have the same name as a private synonym name or a table name.

In order to create and use synonyms, the user must have the correct privileges, which are described in Object Privileges for Synonyms in the Oracle TimesTen In-Memory Database Security Guide.

After synonyms are created, they can be viewed using the following views:

• SYS.ALL_SYNONYMS: describes the synonyms accessible to the current user. See SYS.ALL_SYNONYMS in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

• SYS.DBA_SYNONYMS: describes all synonyms in the database. See SYS.DBA_SYNONYMS in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

• SYS.USER_SYNONYMS: describes the synonyms owned by the current user. See SYS.USER_SYNONYMS in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

The following sections describe using synonyms in TimesTen.

• Creating Synonyms

• Dropping Synonyms

• Synonyms May Cause Invalidation or Recompilation of SQL Queries

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.

Dropping Synonyms

Use the DROP SYNONYM statement to drop an existing synonym from the database. A user cannot be dropped unless all objects, including synonyms, owned by this user are dropped.

For example, the following drops the public synonym pubemp:

DROP PUBLIC SYNONYM pubemp;
Synonym dropped.

In order drop a public synonym or a private synonym in another user's schema, the user must have the appropriate privileges. See DROP SYNONYM in the Oracle TimesTen In-Memory Database SQL Reference.

Synonyms May Cause Invalidation or Recompilation of SQL Queries

When a synonym or object is newly created or dropped, some SQL queries and DDL statements may be invalidated or recompiled.

The following lists the invalidation and recompilation behavior for SQL queries and DDL statements:

1. All SQL queries that depend on a public synonym are invalidated if you create a private synonym with the same name for one of the following objects:

   • private synonym

   • table

   • view

   • sequence

   • materialized view

   • cache group

   • PL/SQL object including procedures, functions, and packages

2. All SQL queries that depend on a private synonym or schema object are invalidated when a private synonym or schema object is dropped.