Dropping Tables

To drop a table that is no longer needed, use the SQL command DROP TABLE. The following statement drops the EMP table:

DROP TABLE emp;

If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, include the CASCADE option in the DROP TABLE command, as shown below:

DROP TABLE emp CASCADE CONSTRAINTS;

Warning: Before dropping a table, familiarize yourself with the consequences of doing so:

• Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.

• All indexes and triggers associated with a table are dropped.

• All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable). See for information about how Oracle manages such dependencies.

• All synonyms for a dropped table remain, but return an error when used.

• All extents allocated for a non-clustered table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects.

• All rows corresponding to a clustered table are deleted from the blocks of the cluster.