Oracle7 Server Application Developer's Guide

Managing Synonyms

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package. The following sections explain how to create, use, and drop synonyms using SQL commands.

Creating Synonyms

Use the SQL command CREATE SYNONYM to create a synonym. The following statement creates a public synonym named PUBLIC_EMP on the EMP table contained in the schema of JWARD:

CREATE PUBLIC SYNONYM public_emp FOR jward.emp;

Privileges Required to Create a Synonym

You must have the CREATE SYNONYM system privilege to create a private synonym in your schema, or the CREATE ANY SYNONYM system privilege to create a private synonym in another user's schema. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Using Synonyms

A synonym can be referenced in a SQL statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMP refers to a table or view, the following statement is valid:

INSERT INTO emp (empno, ename, job)
   VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');

If the synonym named FIRE_EMP refers to a standalone procedure or package procedure, you could execute it in SQL*Plus or Server Manager with the command

EXECUTE fire_emp(7344);

Privileges Required to Use a Synonym

You can successfully use any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the underlying object, either explicitly, from an enabled role, or from PUBLIC. You can also reference any private synonym contained in another schema if you have been granted the necessary object privileges for the private synonym. You can only reference another user's synonym using the object privileges that you have been granted. For example, if you have the SELECT privilege for the JWARD.EMP synonym, you can query the JWARD.EMP synonym, but you cannot insert rows using the synonym for JWARD.EMP.

Dropping Synonyms

To drop a synonym, use the SQL command DROP SYNONYM. To drop a private synonym, omit the PUBLIC keyword; to drop a public synonym, include the PUBLIC keyword. The following statement drops the private synonym named EMP:

DROP SYNONYM emp;

The following statement drops the public synonym named PUBLIC_EMP:

DROP PUBLIC SYNONYM public_emp;

When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain (for example, views and procedures) but become invalid.

Privileges Required to Drop a Synonym

You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the DROP ANY SYNONYM system privilege. To drop a public synonym, you must have the DROP PUBLIC SYNONYM system privilege.