Oracle/PLSQL: Synonyms
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym.
You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, EXPLAIN PLAN, and LOCK TABLE.
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.
The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:
Prerequisites
To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.
To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege.
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.
Restriction on Replacing a Synonym
You cannot use the OR REPLACE clause for a type synonym that has any dependent tables or valid user-defined object types.
PUBLIC
Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.
Oracle uses a public synonym only when resolving references to an object if the object is not prefaced by a schema and the object is not followed by a database link.
If you omit this clause, then the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.
|
|
|
Dropping a synonym
Synonyms are very useful in both distributed and nondistributed database environments because they hide the identity of the underlying object, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to function without modification.
Synonyms can also simplify SQL statements for users in a distributed database system. The following example shows how and why public synonyms are often created by a database administrator to hide the identity of a base table and reduce the complexity of SQL statements.
It is also possible to drop a synonym. The syntax for dropping a synonym is:
drop [public] synonym [schema .] synonym_name [force];
For example:
drop public synonym clients;
This statement drop the synonym called client that we defined earlier.
|
|
|
|
|
|
|
|