|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
Caution: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 "Managing Object Dependencies" for information about how the database manages dependencies.
All synonyms for a dropped table remain, but return an error when used.
All extents allocated for a 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. Clustered tables are the subject of Chapter 22, "Managing Clusters".
The following statement drops the
DROP TABLE hr.int_admin_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, then include the
CASCADE clause in the
DROP TABLE statement, as shown below:
DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;
When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the
TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the
DROP TABLE statement, include the
PURGE clause as shown in the following statement:
DROP TABLE hr.admin_emp PURGE;
Perhaps instead of dropping a table, you want to truncate it. The
TRUNCATE statement provides a fast, efficient method for deleting all rows from a table, but it does not affect any structures associated with the table being truncated (column definitions, constraints, triggers, and so forth) or authorizations. The
TRUNCATE statement is discussed in "Truncating Tables and Clusters".