Oracle DROP SYNONYM

Summary: in this tutorial, you will learn how to use the Oracle DROP SYNONYM statement to remove a synonym from the database.

Introduction to Oracle DROP SYNONYM statement #

The DROP SYNONYM 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 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.