Skip Headers
Oracle® Database SQL Reference
10g Release 2 (10.2)

Part Number B14200-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

TRUNCATE

Caution:

You cannot roll back a TRUNCATE statement.

Purpose

Use the TRUNCATE statement to remove all rows from a table or cluster. By default, Oracle Database also performs the following tasks:

Removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

See Also:

  • DELETE and DROP TABLE for information on other ways to drop table data from the database

  • DROP CLUSTER for information on dropping cluster tables

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

truncate::=

Description of truncate.gif follows
Description of the illustration truncate.gif

Semantics

TABLE Clause

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

Restrictions on Truncating Tables This statement is subject to the following restrictions:

MATERIALIZED VIEW LOG Clause

The MATERIALIZED VIEW 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 or 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.

Note:

The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

PRESERVE Specify PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.

PURGE Specify PURGE if any materialized view log should be purged when the master table is truncated.

See Also:

Oracle Database Advanced Replication for more information about materialized view logs and the TRUNCATE statement

CLUSTER Clause

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.

STORAGE Clauses

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 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 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 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 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.

Examples

Truncating a Table: Example The following statement removes all rows from a hypothetical copy of the sample table hr.employees and returns the freed space to the tablespace containing employees:

TRUNCATE TABLE employees_demo; 

The preceding statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.

Retaining Free Space After Truncate: 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.

Preserving Materialized View Logs After Truncate: Example The following statements are examples of TRUNCATE statements that preserve materialized view logs:

TRUNCATE TABLE sales_demo PRESERVE MATERIALIZED VIEW LOG; 

TRUNCATE TABLE orders_demo;