Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 8 of 11
To create a synonym. A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, or another synonym. For general information on synonyms, see Oracle8i Concepts.
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 4-4 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.
|
creates 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 |
is 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 |
synonym |
is 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. |
|
identifies 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: |
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. See Also:
|
|
If you omit dblink, Oracle assumes the object is located on the local database. Restriction: You cannot specify dblink for a Java class synonym. |
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;
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.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|