Oracle7 Server SQL Reference

CREATE SYNONYM

Purpose

To create a synonym. A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, snapshot, or another synonym.

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. If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of schema to contain the synonym.

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

Syntax

Keywords and Parameters

PUBLIC

creates a public synonym. Public synonyms are accessible to all users. If you omit this option, the synonym is private and is accessible only within its schema.

schema

is the schema to contain the synonym. If you omit schema, Oracle7 creates the synonym in your own schema. You cannot specify schema if you have specified PUBLIC.

synonym

is the name of the synonym to be created.

FOR

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

table

view

sequence

stored procedure, function , or package

snapshot

synonym

The object cannot be contained in a package.

Note that the object need not currently exist and you need not have privileges to access the object.

You can use a complete or partial dblink to create a synonym for an object on a remote database where the object is located. For more information on referring to database links, see the section, "Referring to Objects in Remote Databases," . If you specify dblink and omit schema, the synonym refers to an object in the schema specified by the database link. It is recommended that you specify the schema containing the object in the remote database.

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

Usage Notes

In Trusted Oracle7, the new synonym is automatically labeled with your DBMS label.

A synonym can be used to stand for its base object in any of the following Data Manipulation Language statements:

SELECT

INSERT

UPDATE

DELETE

EXPLAIN PLAN

LOCK TABLE

Synonyms can also be used in the following Data Definition Language statements:

AUDIT

NOAUDIT

GRANT

REVOKE

COMMENT

Synonyms are used for security and convenience. Creating a synonym for an object allows you to:

reference the object without specifying its owner

reference the object without specifying the database on which it is located

provide another name for the object

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.

Scope of Synonyms

A private synonym name must be distinct from all other objects in its schema. Oracle7 attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. Oracle7 only uses a public synonym when resolving references to an object if both of the following cases are true:

the object is not prefaced by a schema

the object is not followed by a database link

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

Example I

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

Example II

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

Note that a synonym may have the same name as the base table provided the base table is contained in another schema.