|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: SAVEPOINT to UPDATE, 7 of 8
TRUNCATE statement to remove all rows from a table or cluster. By default, Oracle also deallocates all space used by the removed rows except that specified by the
MINEXTENTS storage parameter and sets the
NEXT storage parameter to the size of the last extent removed from the segment by the truncation process.
Removing rows with the
TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its storage parameters. Truncating has none of these effects.
To truncate a table or cluster, the table or cluster must be in your schema or you must have
TABLE system privilege.
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit
schema, then Oracle assumes the table is in your own cluster.
NEXTstorage parameter of
tableto be the size of the last extent deleted from the segment in the process of truncation.
UNUSABLEindicators for the following indexes on
table: range and hash partitions of local indexes and subpartitions of local indexes.
tableis not empty, then Oracle marks
UNUSABLEall nonpartitioned indexes and all partitions of global partitioned indexes on the table.
Oracle9i Data Cartridge Developer's Guide for more information on domain indexes
table(whether it is a regular or index-organized table) contains LOB columns, then all LOB data and LOB index segments are truncated.
tableis partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
When you truncate a table, Oracle automatically removes all data in the table's indexes and any materialized view direct-path
Restrictions on truncating tables:
tablebelongs to a hierarchy, then it must be the root of the hierarchy.
table, then neither the index nor any index partitions can be marked
LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.
PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.
PURGE if any materialized view log should be purged when the master table is truncated.
Oracle9i Replication for more information about materialized view logs and the
Specify the schema and name of the cluster to be truncated. You can truncate only an indexed cluster, not a hash cluster. If you omit
schema, then Oracle assumes the cluster is in your own schema.
When you truncate a cluster, Oracle also automatically deletes all data in the indexes of the cluster tables.
STORAGE clauses let you determine what happens to the space freed by the truncated rows. The
STORAGE clause and
STORAGE clause also apply to the space freed by the data deleted from associated indexes.
STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the
MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle also sets the
NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.
STORAGE to retain the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.
If you have specified more than one free list for the object you are truncating, then the
The following statement removes all rows from a copy of the sample table
hr.employees and returns the freed space to the tablespace containing
The preceding statement also removes all data from all indexes on
employees and returns the freed space to the tablespaces containing them.
The following statement removes all rows from all tables in the
personnel cluster, but leaves the freed space allocated to the tables:
The preceding statement also removes all data from all indexes on the tables in the
The following statements are examples of truncate statements that preserve materialized view logs: