Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF



Use the DROP TABLE statement to remove a table or an object table and all its data from the database.


You cannot roll back a DROP TABLE statement.


For an external table, this statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.


You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ABORT statement.

Dropping a table invalidates the table's dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the table's indexes, integrity constraints, and triggers, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table.

See Also:
  • CREATE TABLE for information on creating tables
  • ALTER TABLE for information on modifying tables
  • TRUNCATE and DELETE for information on how to remove data from a table without dropping the table


The table must be in your own schema or you must have the DROP ANY TABLE system privilege.



Text description of statements_96.gif follows
Text description of drop_table



Specify the schema containing the table. If you omit schema, then Oracle assumes the table is in your own schema.


Specify the name of the table, object table, or index-organized table to be dropped. Oracle automatically performs the following operations:

Restriction on Dropping Tables

You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause.


Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then Oracle returns an error and does not drop the table.


Dropping a Table: Example

The following statement drops the oe.list_customers table created in "List Partitioning Example".

DROP TABLE list_customers;