Delete a synonym

You can use the DROP SYNONYM statement to drop a synonym. This topic describes how to drop a synonym.

Prerequisites

• To drop a private synonym, you must be the owner of this private synonym, or have the DROP ANY SYNONYM system privilege.

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

Syntax

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

Parameters

Examples

Example 1

Drop a private synonym.

1. View the sp synonym.

   obclient> SELECT * FROM USER_SYNONYMS WHERE SYNONYM_NAME = 'SP';
   +--------------+-------------+------------+---------+---------------+
   | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK | ORIGIN_CON_ID |
   +--------------+-------------+------------+---------+---------------+
   | SP           | SYS         | DWS_NY     | NULL    |             0 |
   +--------------+-------------+------------+---------+---------------+
   1 row in set
   

2. Drop the sp synonym.

   obclient> DROP SYNONYM sp;
   Query OK, 0 rows affected
   

3. View the sp synonym again.

   obclient> SELECT * FROM USER_SYNONYMS WHERE SYNONYM_NAME = 'SP';
   Empty set
   

Example 2

Drop a public synonym.

1. View the spb synonym.

   obclient> SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = 'SPB';
   +--------+--------------+-------------+------------+---------+
   | OWNER  | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
   +--------+--------------+-------------+------------+---------+
   | PUBLIC | SPB          | SYS         | DWS_NY1    | NULL    |
   +--------+--------------+-------------+------------+---------+
   1 row in set
   

2. Drop the spb synonym.

   obclient> DROP PUBLIC SYNONYM spb;
   Query OK, 0 rows affected
   

3. View the spb synonym again.

   obclient> SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = 'SPB';
   Empty set