TRUNCATE statement

TRUNCATE 
TABLE  [ owner.]table-name
| MATERIALIZED VIEW [ owner.]materialized-view-name

The TRUNCATE statement deletes all rows from the table or materialized view.

Note

The TRUNCATE TABLE statement should be used with great care on a database involved in synchronization or replication because the statement deletes all rows from a table, similar to a DELETE statement that doesn't have a WHERE clause. However, no triggers are fired as a result of a TRUNCATE statement. Furthermore, the row deletions are not entered into the transaction log and therefore are not synchronized or replicated. This can lead to inconsistencies that can cause synchronization or replication to fail.

After a TRUNCATE statement, the object's schema and all the indexes continue to exist until you execute a DROP statement. The schema definitions and constraints remain intact, and triggers and permissions remain in effect.

table-name can be the name of a base table or a temporary table.

With TRUNCATE TABLE, if all the following criteria are satisfied, a fast form of table truncation is executed:

There are no foreign keys either to or from the table.
The TRUNCATE TABLE statement is not executed within a trigger.
The TRUNCATE TABLE statement is not executed within an atomic statement.

If a fast truncation is carried out, individual DELETEs are not recorded in the transaction log, and a COMMIT is carried out before and after the operation. Fast truncation cannot be used within snapshot transactions.

If you attempt to use TRUNCATE TABLE on a table on which an immediate text index is built, or that is referenced by an immediate view, the truncation fails. This does not occur for non-immediate text indexes or materialized views; however, it is strongly recommended that you truncate the data in dependent indexes and materialized views before executing the TRUNCATE TABLE statement on a table, and then refreshing the indexes and materialized views after.

Must be the table owner, or have DBA authority, or have ALTER permissions on the table.
For base tables and materialized views, the TRUNCATE statement requires exclusive access to the table, as the operation is atomic (either all rows are deleted, or none are). This means that any cursors that were previously opened and that reference the table being truncated must be closed and a COMMIT or ROLLBACK must be executed to release the reference to the table.
For temporary tables, each user has their own copy of the data, and exclusive access is not required when executing the TRUNCATE statement.

When you truncate a materialized view, you change the status of the view to uninitialized.
Delete triggers are not fired by the TRUNCATE statement.
A COMMIT is performed before and after a TRUNCATE statement is executed.
Individual deletions of rows are not entered into the transaction log, so the TRUNCATE operation is not replicated. Do not use this statement in SQL Remote replication or on a MobiLink remote database.
If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, the truncation operation resets the next available value for the column.

SQL/2008 The TRUNCATE TABLE statement is optional language feature F200 of the SQL/2008 standard. TRUNCATE MATERIALIZED VIEW is a vendor extension.

Delete all rows from the Departments table:

TRUNCATE TABLE Departments;

TRUNCATE statement

Syntax

Remarks

Note

Permissions

Side effects

See also

Standards and compatibility

Example