Oracle / PLSQL: DROP TABLE Statement

This Oracle tutorial explains how to use the Oracle DROP TABLE statement with syntax and examples.

Description

The Oracle DROP TABLE statement allows you to remove or delete a table from the Oracle database.

Syntax

The syntax for the Oracle DROP TABLE statement is:

DROP TABLE [schema_name].table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];

Parameters or Arguments

schema_name

The name of the schema that owns the table.

table_name

The name of the table to remove from the Oracle database.

CASCADE CONSTRAINTS

Optional. If specified, all referential integrity constraints will be dropped as well.

PURGE

Optional. If specified, the table and its dependent objects will be purged from the recycle bin and you will not be able to recover the table. If not specified, the table and its dependent objects are placed in the recycle bin and can be recovered later, if needed.

Note

• If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error and Oracle will not drop the table.

Example

Let's look at an example that shows how to drop a table in Oracle by using the DROP TABLE statement.

For example:

DROP TABLE customers;

This Oracle DROP TABLE example would drop the table called customers.

Purge

Let's look at how to use the PURGE option with the DROP TABLE statement in Oracle.

When issuing a DROP TABLE statement in Oracle, you can specify the PURGE option. The PURGE option will purge the table and its dependent objects so that they do not appear in the recycle bin. The risk of specifying the PURGE option is that you will not be able to recover the table. However, the benefit of using PURGE is that you can ensure that sensitive data will not be left sitting in the recycle bin.

For example:

DROP TABLE customers PURGE;

This DROP TABLE statement would drop the table called customers and issue a PURGE so that the space associated with the customers table is released. In other words, the customers table is not placed into the recycle bin and, therefore, can not be recovered later if required.