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, 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.

Table 10-1 lists the SQL statements in which you can refer to synonyms.

Table 10-1 Using Synonyms

DML Statements	DDL Statements
SELECT	AUDIT
INSERT	NOAUDIT
UPDATE	GRANT
DELETE	REVOKE
EXPLAIN PLAN	COMMENT
LOCK TABLE

See Also: Oracle8i 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

Keywords and Parameters

PUBLIC

Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users.

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, the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.

schema

Specify the schema to contain the synonym. If you omit schema, 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, 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.

FOR object

Specify the object for which the synonym is created. If you do not qualify object with schema, Oracle assumes that the schema object is in your own schema. The schema object can be of the following types:

• Table or object table

• View or object view

• Sequence

• Stored procedure, function, or package

• Materialized view

• Java class schema object

• Synonym

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

Restrictions:

• The schema object cannot be contained in a package.

You cannot create a synonym for an object type.

dblink

You can use a complete or partial dblink to create a synonym for a schema object on a remote database where the object is located. If you specify dblink and omit schema, 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, Oracle assumes the object is located on the local database.

Restriction: You cannot specify dblink for a Java class synonym.

See Also: "Referring to Objects in Remote Databases" for more information on referring to database links CREATE DATABASE LINK for more information on creating database links

Examples

CREATE SYNONYM Examples

To define the synonym market for the table market_research in the schema scott, issue the following statement:

CREATE SYNONYM market 
   FOR scott.market_research;

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

CREATE PUBLIC SYNONYM emp 
   FOR scott.emp@sales;

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, assume the schemas scott and blake each contain tables named dept and the user SYSTEM creates a PUBLIC synonym named dept for blake.dept. If the user scott then issues the following statement, Oracle returns rows from scott.dept:

SELECT * FROM dept;

To retrieve rows from blake.dept, the user scott must preface dept with the schema name:

SELECT * FROM blake.dept;

If the user adam's schema does not contain an object named dept, then adam can access the dept table in blake's schema by using the public synonym dept:

SELECT * FROM dept;