TRUNCATE

Caution: You cannot roll back a TRUNCATE statement.

Purpose

Use the TRUNCATE statement to remove all rows from a table or cluster and reset the STORAGE parameters to the values when the table or cluster was created.

Deleting 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 constraint, and triggers and respecify its storage parameters. Truncating has none of these effects.

See Also: DELETE, DROP CLUSTER, and DROP TABLE

Prerequisites

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

Syntax

Keywords and Parameters

TABLE

Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema, Oracle assumes the table is in your own cluster.

• You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are truncated.

• The table's storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.

• Oracle also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.

• If table is not empty, Oracle marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table.

• For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.

  See Also: Oracle8i Data Cartridge Developer's Guide

• If table (whether it is a regular or index-organized table) contains LOB columns, all LOB data and LOB index segments will be truncated.

• If table is partitioned, all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, will be truncated.

  Note: When you truncate a table, Oracle automatically deletes all data in the table's indexes and any materialized view direct-load INSERT information held in association with the table. (This information is independent of any materialized view/snapshot log.) If this direct-load INSERT information is deleted, an incremental refresh of the materialized view may lose data.

Restrictions:

• You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

• You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

• You cannot truncate a table if any domain indexes defined on any of its columns are marked LOADING or FAILED.

SNAPSHOT LOG

The SNAPSHOT LOG clause lets you specify whether a snapshot log defined on the table is to be preserved or purged when the table is truncated. This clause allows snapshot master tables to be reorganized through export/import without affecting the ability of primary-key snapshots defined on the master to be fast refreshed. To support continued fast refresh of primary-key snapshots, the snapshot log must record primary-key information.

PRESERVE	Specify PRESERVE if any snapshot log should be preserved when the master table is truncated. This is the default.
PURGE	Specify PURGE if any snapshot log should be purged when the master table is truncated.

CLUSTER

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, Oracle assumes the table is in your own cluster.

When you truncate a cluster, Oracle also automatically deletes all data in the cluster's tables' indexes.

STORAGE Clauses

DROP STORAGE	Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the table's or cluster's MINEXTENTS parameter. This space can subsequently be used by other objects in the tablespace. This is the default.
REUSE STORAGE	Specify REUSE 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 inserts or updates.
	Note: If you have specified more than one free list for the object you are truncating, 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.

The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.

Examples

Simple TRUNCATE Example

The following statement deletes all rows from the emp table and returns the freed space to the tablespace containing emp:

TRUNCATE TABLE emp; 

The above statement also deletes all data from all indexes on emp and returns the freed space to the tablespaces containing them.

Retaining free space after truncating

The following statement deletes all rows from all tables in the cust cluster, but leaves the freed space allocated to the tables:

TRUNCATE CLUSTER cust REUSE STORAGE 

The above statement also deletes all data from all indexes on the tables in cust.

Preserving materialized view logs after truncating

The following statements are examples of truncate statements that preserve snapshot logs:

TRUNCATE TABLE emp PRESERVE SNAPSHOT LOG; 
TRUNCATE TABLE stock;