只问耕耘
分类: Oracle
2010-01-12 11:12:12
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:
Use the DELETE
statement.
Use the DROP
and CREATE
statements.
Use the TRUNCATE
statement.
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 emp
table:
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 TRUNCATE
and DROP
affect the entire object.
See Also:
for syntax and other information about theDELETE
statementYou can drop a table and then re-create the table. For example, the following statements drop and then re-create the emp
table:
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.
You can delete all rows of the table using the TRUNCATE
statement. For example, the following statement truncates the emp
table:
TRUNCATE TABLE emp;
Using the 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.
You can truncate any table or cluster in your own schema. Any user who has the DROP ANY TABLE
system privilege can truncate a table or cluster in any schema.
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.
As a 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. See the Oracle Database Security Guide for information about auditing.
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.
The REUSE STORAGE
or DROP STORAGE
options of the TRUNCATE
statement control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation. The default option, DROP STORAGE
, 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.
Alternatively, the REUSE STORAGE
option 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;
The REUSE
or DROP STORAGE
option also applies 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.