Dropping Tables

Use DROP TABLE to delete an entire table from the database.

Note:
If you want to drop columns from a table, use ALTER TABLE.

Dropping a Table

Use DROP TABLE to remove the data, metadata, and indexes of a table from a database. It also drops any triggers that are based on the table. A table can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

You cannot drop a table that is referenced in a computed column, a view, integrity constraint, or stored procedure. You cannot drop a table that is being used by an active transaction until the table is no longer in use.

DROP TABLE fails and returns an error if:

The person who attempts to drop the table is not the owner of the table.
The table is in use when the drop is attempted. The drop is postponed until the table is no longer in use.
The table has a UNIQUE or PRIMARY KEY defined for it, and the PRIMARY KEY is referenced by a FOREIGN KEY in another table. First drop the FOREIGN KEY constraints in the other table, then drop the table.
The table is used in a view, trigger, stored procedure, or computed column. Remove the other elements before dropping the table.
The table is referenced in the CHECK constraint of another table.

Note:
DROP TABLE does not delete external tables; it removes the table definition from the database. You must explicitly delete the external file.

DROP TABLE Syntax

DROP TABLE name;

The following statement drops the table, COUNTRY:

DROP TABLE COUNTRY;

Advance To:

Global Temporary Tables