Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
Caution:
You cannot roll back aTRUNCATE CLUSTER
statement.Use the TRUNCATE CLUSTER
statement to remove all rows from a cluster. By default, Oracle Database also performs the following tasks:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS
storage parameter
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 cluster. Dropping and re-creating a cluster invalidates dependent objects of the cluster, requires you to regrant object privileges on the cluster, and requires you to re-create the indexes and cluster on the table and respecify its storage parameters. Truncating has none of these effects.
Removing rows with the TRUNCATE CLUSTER
statement can be faster than removing all rows with the DELETE
statement, especially if the cluster has numerous indexes and other dependencies.
See Also:
DELETE and DROP CLUSTER for information on other ways of dropping data from a cluster
TRUNCATE TABLE for information on truncating a table
To truncate a cluster, the cluster must be in your schema or you must have DROP
ANY
TABLE
system privilege.
See Also:
"Restrictions on Truncating Tables"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 the database assumes the cluster is in your own schema.
When you truncate a cluster, the database also automatically deletes all data in the indexes of the cluster tables.
The STORAGE
clauses let you determine what happens to the space freed by the truncated rows. The DROP
STORAGE
clause and REUSE
STORAGE
clause also apply to the space freed by the data deleted from associated indexes.
DROP STORAGE Specify DROP
STORAGE
to deallocate all space from the deleted rows from the cluster except the space allocated by the MINEXTENTS
parameter of the cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database 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.
REUSE STORAGE Specify REUSE
STORAGE
to retain the space from the deleted rows allocated to the 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 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 REUSE
STORAGE
clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
Truncating a Cluster: Example The following statement removes all rows from all tables in the personnel
cluster, but leaves the freed space allocated to the tables:
TRUNCATE CLUSTER personnel REUSE STORAGE;
The preceding statement also removes all data from all indexes on the tables in the personnel
cluster.