|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
You can delete all rows of a table or all rows in a group of clustered tables so that the table (or cluster) still exists, but is completely empty. For example, consider a table that contains monthly data, and at the end of each month, you need to empty it (delete all rows) after archiving its data.
To delete all rows from a table, you have the following options:
These options are discussed in the following sections
You can delete the rows of a table using the
DELETE statement. For example, the following statement deletes all rows from the
DELETE FROM emp;
If there are many rows present in a table or cluster when using the
DELETE statement, significant system resources are consumed as the rows are deleted. For example, CPU time, redo log space, and undo segment space from the table and any associated indexes require resources. Also, as each row is deleted, triggers can be fired. The space previously allocated to the resulting empty table or cluster remains associated with that object. With
DELETE you can choose which rows to delete, whereas
DROP affect the entire object.
See Also:Oracle Database SQL Language Reference for syntax and other information about the
You can drop a table and then re-create the table. For example, the following statements drop and then re-create the
DROP TABLE emp; CREATE TABLE emp ( ... );
When dropping and re-creating a table or cluster, all associated indexes, integrity constraints, and triggers are also dropped, and all objects that depend on the dropped table or clustered table are invalidated. Also, all grants for the dropped table or clustered table are dropped.
TRUNCATE TABLE emp;
TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A
TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A
TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A
TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
Before truncating a table or clustered table containing a parent key, all referencing foreign keys in different tables must be disabled. A self-referential constraint does not have to be disabled.
TRUNCATE statement deletes rows from a table, triggers associated with the table are not fired. Also, a
TRUNCATE statement does not generate any audit information corresponding to
DELETE statements if auditing is enabled. Instead, a single audit record is generated for the
TRUNCATE statement being issued.
A hash cluster cannot be truncated, nor can tables within a hash or index cluster be individually truncated. Truncation of an index cluster deletes all rows from all tables in the cluster. If all the rows must be deleted from an individual clustered table, use the
DELETE statement or drop and re-create the table.
These options also apply to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. The storage parameters for a truncated table, cluster, or associated indexes are not changed as a result of the truncation.
TRUNCATE options are:
DROP STORAGE, the default option, reduces the number of extents allocated to the resulting table to the original setting for
MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.
STORAGE drops the segment. In addition to the
STORAGE also applies to the
(SUB)PARTITION statement. This option also drops any dependent object segments associated with the partition being truncated.
STORAGE is not supported for clusters.
Note:This functionality is available with Oracle Database 11g release 2 (220.127.116.11).
TRUNCATE TABLE emp DROP ALL STORAGE;
REUSE STORAGE specifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates the
emp_dept cluster, leaving all extents previously allocated for the cluster available for subsequent inserts and deletes:
TRUNCATE CLUSTER emp_dept REUSE STORAGE;