DROP SCHEMA

Use the DROP SCHEMA command to drop a schema. When you drop a schema, you drop all the objects defined within that schema. You cannot drop a schema if it is being accessed in other user sessions or if it is the default schema of the database.

Syntax

Syntax for dropping a schema:

DROP SCHEMA [<database_name>.]<schema_name> { CASCADE | RESTRICT }

Inputs

The DROP SCHEMA command takes the following inputs:

Table 1. DROP SCHEMA inputs
Input	Description
<database_name>	The name of the database that contains the schema to be dropped. The default is the current database.
<schema_name>	The name of the schema to be dropped
CASCADE	Drops all the objects defined in the schema, and then drops the schema.
RESTRICT	Drops the schema if it is empty; otherwise returns an error that the schema cannot be dropped.

Outputs

The DROP SCHEMA command produces the following outputs:

Table 2. DROP SCHEMA outputs
Output	Description
`DROP SCHEMA`	The command was successful.
`ERROR: Drop behaviour (RESTRICT \| CASCADE) needs to be specified`	You must specify the RESTRICT or CASCADE option to drop the schema.
ERROR: DROP SCHEMA: current schema 'db'.'schema' cannot be dropped.	You specified the RESTRICT option but the system cannot drop the schema because it is not empty.

Privileges

You must be the admin user, the owner of the schema, the owner of the database in which the schema is defined, or your account must have Drop privileges on the schema or the Schema object class. If a schema has the same name as an existing database user account and if the enable_schema_user setting is TRUE, you must be the admin user.