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.

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

SQLRF54936drop_synonym::=

Semantics

SQLRF54937PUBLIC

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

SQLRF54938schema

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

SQLRF54939synonym

Specify the name of the synonym to be dropped.

If you drop a synonym for the master table of a materialized view, and if the defining query of the materialized view specified the synonym rather than the actual table name, then Oracle Database marks the materialized view unusable.

If an object type synonym has any dependent tables or user-defined types, then you cannot drop the synonym unless you also specify FORCE.

SQLRF54940FORCE

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

Example

SQLRF54941Dropping a Synonym: Example To drop the public synonym named customers, which was created in "Oracle Database Resolution of Synonyms: Example", issue the following statement:

DROP PUBLIC SYNONYM customers;