Oracle7 Server Administrator's Guide

Managing Synonyms

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.

This section includes the following synonym management information:

Creating Synonyms

Dropping Synonyms

Creating Synonyms

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

Create a synonym using the SQL command CREATE SYNONYM. For example, 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;

Dropping Synonyms

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.

Drop a synonym that is no longer required using the SQL command DROP SYNONYM. To drop a private synonym, omit the PUBLIC keyword; to drop a public synonym, include the PUBLIC keyword.

For example, 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; however, they become invalid (not usable).

See Also: For more information about how dropping synonyms can affect other schema objects, see "Managing Object Dependencies" .