Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE SYNONYM to CREATE TRIGGER, 2 of 6
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 14-1 lists the SQL statements in which you can refer to synonyms.
DML Statements | DDL Statements |
|
|
|
|
|
|
|
|
|
|
|
|
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.
create_synonym::=
create_synonym
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.
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
.
Specify the name of the synonym to be created.
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:
The schema object need not currently exist and you need not have privileges to access the object.
Restrictions:
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, 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:
|
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 SALES
database, you could issue the following statement:
CREATE PUBLIC SYNONYM employees FOR hr.employees@sales;
A synonym may have the same name as the base table, provided the base table is contained in another schema.
Oracle attempts to resolve references to objects at the schema level before resolving them at the PUBLIC
synonym level. For example, assume the schemas oe
and hr
each contain tables named locations
and the user SYSTEM
creates a PUBLIC
synonym named locations
for oe.locations
:
CREATE PUBLIC SYNONYM locations FOR oe.locations;
If the user hr
then issues the following statement, Oracle returns rows from hr.locations
:
SELECT * FROM locations;
To retrieve rows from oe.locations
, the user hr
must preface locations
with the schema name:
SELECT * FROM oe.locations;
If the user pm
's schema does not contain an object named locations
, then pm
can access the locations
table in oe
's schema by using the public synonym locations
:
SELECT * FROM locations;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|