How to DROP SYNONYM in Oracle?

Introduction to Oracle DROP SYNONYM statement

The DROP SYNONYM the statement allows you to delete a synonym from the database. Here is the basic syntax of the DROP SYNONYM statement:

DROP SYNONYM schema.synonym_name FORCE;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the synonym that you want to remove after the DROP SYNONYM keyword. If the synonym belongs to a schema, you must specify its schema name. If you skip the schema name, Oracle will assume that you delete the synonym in your own schema.
• Second, use the FORCE keyword to delete the synonym even if it has dependent tables or user-defined types.

To drop a public synonym, you use the PUBLIC the keyword as follows:

DROP PUBLIC SYNONYM synonym_name FORCE;
Code language: SQL (Structured Query Language) (sql)

Note that you cannot specify the schema name when you use the PUBLIC keyword.

If you want to drop a private synonym, you must be the owner of the schema to which the synonym belongs or you must have the DROP ANY SYNONYM privilege. In case you want to drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM privilege.

Oracle DROP SYNONYM example

The following example uses the DROP SYNONYM statement to delete the stocks synonym created in the CREATE SYNONYM tutorial:

DROP SYNONYM stocks;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following message:

Synonym STOCKS dropped.
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle DROP SYNONYM statement to delete a synonym from the database.