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
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
andCREATE 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:
-
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
-
-
All SQL queries that depend on a private synonym or schema object are invalidated when a private synonym or schema object is dropped.