Purpose
This statement is used to drop a synonym.
Syntax
DROP [PUBLIC] SYNONYM [schema_name.]synonym_name [FORCE]
Notice
To drop a synonym, you must have the following privileges:
- To drop a private synonym:
- The
SYNONYMto be dropped must exist in the corresponding schema. - You must have the
DROP ANY SYNONYMprivilege.
- The
- To drop a public synonym:
- You must specify the
PUBLICkeyword and cannot specify aschema. - You must have the
DROP PUBLIC SYNONYMprivilege.
- You must specify the
Parameters
| Parameter | Description |
|---|---|
| PUBLIC | Specifies PUBLIC to drop a public synonym. If you do not specify PUBLIC, a private synonym is dropped. |
| schema | Specifies the schema in which the synonym is located. If you omit schema., the synonym is assumed to be in your own schema. If you specify PUBLIC, you do not need to specify schema for the synonym. |
| synonym_name | synonym_name specifies the name of the synonym. |
| FORCE | Specifies FORCE to drop a synonym that has dependent tables or user-defined types. |
Examples
Create and drop a private synonym.
-- Create a table obclient> CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50) ); -- Create and drop a private synonym obclient> CREATE SYNONYM syn1 FOR employees; obclient> DROP SYNONYM syn1;Create and drop a
PUBLICsynonym.-- Create a table obclient> CREATE TABLE departments ( dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(50) ); -- Create and drop a public synonym obclient> CREATE PUBLIC SYNONYM syn2 FOR departments; obclient> DROP PUBLIC SYNONYM syn2;