Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

CREATE SYNONYM

Purpose

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.

You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.

See Also:

Oracle9i Database Concepts for general information on synonyms

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.

To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.

Syntax

create_synonym::=

Text description of statements_74.gif follows
Text description of create_synonym


Semantics

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.

Notes on Creating Public Synonyms

schema

Specify the schema to contain the synonym. If you omit schema, then Oracle creates the synonym in your own schema. You cannot specify a schema for the synonym if you have specified PUBLIC.

synonym

Specify the name of the synonym to be created.


Caution:

The functional maximum length of the synonym name is 32 bytes. Names longer than 30 bytes are permitted for Java functionality only. If you specify a name longer than 30 bytes, then Oracle encrypts the name and places a representation of the encryption in the data dictionary. The actual encryption is not accessible, and you cannot use either your original specification or the data dictionary representation as the synonym name.


See Also:

"CREATE SYNONYM Examples" and "Resolution of Synonyms Example"

FOR Clause

Specify the object for which the synonym is created. The schema object for which you are creating the synonym can be of the following types:

The schema object need not currently exist and you need not have privileges to access the object.

Restriction on the FOR Clause

The schema object cannot be contained in a package.

schema

Specify the schema in which the object resides. If you do not qualify object with schema, then Oracle assumes that the schema object is in your own schema.


Note:

If you are creating a synonym for a procedure or function on a remote database, then you must specify schema in this CREATE statement. Alternatively, you can create a local public synonym on the database where the object resides. However, the database link must then be included in all subsequent calls to the procedure or function.


dblink

You can specify a complete or partial database link to create a synonym for a schema object on a remote database where the object is located. If you specify dblink and omit schema, then the synonym refers to an object in the schema specified by the database link. Oracle Corporation recommends that you specify the schema containing the object in the remote database.

If you omit dblink, then Oracle assumes the object is located on the local database.

Restriction on Database Links

You cannot specify dblink for a Java class synonym.

See Also:

Examples

CREATE SYNONYM Examples

To define the synonym offices for the table locations in the schema hr, issue the following statement:

CREATE SYNONYM offices 
   FOR hr.locations;

To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table 
   FOR oe.employees@remote.us.oracle.com;

A synonym may have the same name as the base table, provided the base table is contained in another schema.

Resolution of Synonyms Example

Oracle attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. For example, the schemas oe and sh both contain tables named customers. In the next example, user SYSTEM creates a PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

If the user sh then issues the following statement, then Oracle returns the count of rows from sh.customers:

SELECT COUNT(*) FROM customers;

To retrieve the count of rows from oe.customers, the user sh must preface customers with the schema name. (The user sh must have select permission on oe.customers as well.)

SELECT COUNT(*) FROM oe.customers;

If the user hr's schema does not contain an object named customers, and if hr has select permission on oe.customers, then hr can access the customers table in oe's schema by using the public synonym customers:

SELECT COUNT(*) FROM customers;