Oracle / PLSQL: TRUNCATE TABLE Statement

This Oracle tutorial explains how to use the Oracle TRUNCATE TABLE statement with syntax, examples, and practice exercises.

Description

The TRUNCATE TABLE statement is used to remove all records from a table in Oracle. It performs the same function as a DELETE statement without a WHERE clause.

Syntax

The syntax for the TRUNCATE TABLE statement in Oracle/PLSQL is:

TRUNCATE TABLE [schema_name.]table_name
  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
  [ DROP STORAGE | REUSE STORAGE ] ;

Parameters or Arguments

schema_name

Optional. If specified, it is the name of the schema that the table belongs to.

table_name

The table that you wish to truncate.

PRESERVE MATERIALIZED VIEW LOG

Optional. If specified, the materialized view log will be preserved when the table is truncated. This is the default behavior.

PURGE MATERIALIZED VIEW LOG

Optional. If specified, the materialized view log will be purged when the table is truncated.

DROP STORAGE

Optional. If specified, all storage for the truncated rows will be deallocated, except the space that has been allocated by MINEXTENTS. This is the default behavior.

REUSE STORAGE

Optional. If specified, all storage for the truncated rows will remain allocated to the table.

Example

In Oracle, truncating a table is a fast way to clear out records from a table if you don't need to worry about rolling back. One of the reasons is that when the table is truncated, it does not affect any of the table's indexes, triggers, or dependencies. Truncating a table is also a lot easier than dropping the table and recreating it.

Let's look at an example of how to use the TRUNCATE TABLE statement in Oracle/PLSQL.

For example:

TRUNCATE TABLE customers;

This example would truncate the table called customers and remove all records from that table.

It would be equivalent to the following DELETE statement in Oracle:

DELETE FROM customers;

Both of these statements would result in all data from the customers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you can't roll back the TRUNCATE TABLE statement.

Let's look at one more example where we prefix the table name with the schema name.

For example:

TRUNCATE TABLE aodba.suppliers;

This example would truncate the table called suppliers in the schema called aodba. Before you can truncate a table in another schema, you must have the necessary privileges such as DROP ANY TABLE.