TRUNCATE
TRUNCATE {TABLE [schema.]table [{PRESERVE | PURGE} SNAPSHOT LOG] |
        CLUSTER [schema.]cluster
       }
  [{DROP | REUSE} STORAGE]

Removes all rows from a table or cluster.

To execute a TRUNCATE command, you must either be the owner of the table to be truncated, or have the DROP ANY TABLE system privilege. Merely being granted privileges on the table will not be sufficient.

Keywords

table

Specifies the name of the table from which rows are to be removed.

cluster

Specifies the name of the cluster from which rows are to be removed.

PRESERVE SNAPSHOT LOG

Specifies that existing snapshot logs on this table should be preserved when the table is truncated. This option is useful when a table is being reloaded during an EXPort/TRUNCATE/IMPort operation, since a fast refresh will not be triggered.

PURGE SNAPSHOT LOG

Specifies that existing snapshot logs on this table should be purged when the table is truncated.

DROP STORAGE

Deallocates storage used by the rows and returns the space to the free space pool. This option is the default.

REUSE STORAGE

Retains the space used by the deleted rows. This option is useful if the table or cluster will be reloaded with data.

The TRUNCATE statement does not create rollback records, so it cannot be rolled back. This characteristic makes TRUNCATE extremely ...

Get Oracle SQL: the Essential Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial