DROP SYNONYM

Purpose

Use the DROP SYNONYM statement to remove a synonym from the database or to change the definition of a synonym by dropping and re-creating it.

See Also: CREATE SYNONYM for more information on synonyms

Prerequisites

To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege.

To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege.

Syntax

drop_synonym::=

Text description of drop_synonym

Semantics

PUBLIC

You must specify PUBLIC to drop a public synonym. You cannot specify schema if you have specified PUBLIC.

schema

Specify the schema containing the synonym. If you omit schema, then Oracle assumes the synonym is in your own schema.

synonym

Specify the name of the synonym to be dropped.

If you drop a synonym for a materialized view, or its containing table or materialized view, or any of its dependent tables, then Oracle invalidates the materialized view.

Restriction on Dropping Synonyms

You cannot drop a type synonym that has any dependent tables or user-defined types unless you also specify FORCE.

FORCE

Specify FORCE to drop the synonym even if it has dependent tables or user-defined types.

Caution: Oracle does not recommend that you specify FORCE to drop object synonyms with dependencies. This operation can result in invalidation of other user-defined types or marking UNUSED the table columns that depend on the synonym. For information about type dependencies, see Oracle9i Application Developer's Guide - Object-Relational Features.

Example

Dropping a Synonym: Example

To drop the public synonym named customers (created in "Resolution of Synonyms Example"), issue the following statement:

DROP PUBLIC SYNONYM customers;