Oracle 10g Tutorials : Dropping Tables

In this training you will learn about Dropping Tables, Consequences of Dropping a Table, CASCADE Clause and the PURGE Clause.

Dropping Tables

To drop a table that you no longer need, use the DROP TABLE statement. The table must be contained in your schema or you must have the DROP ANY TABLE system privilege.

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).
  .
  
• 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.
  

The following statement drops the admin_emp_dept table:

DROP TABLE admin_emp_dept;

Figure 3. Drop Table

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 admin_emp_dept CASCADE CONSTRAINTS;

Figure 4. Drop Table 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 FLASHBACK 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 admin_emp_dept PURGE;

Figure 5. Drop Table 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.