Oracle TRUNCATE statement allows you to quickly delete all rows of a
table or cluster.
In release 11.2 truncate has been enhanced to allow you to remove all of
the segment space consumed by a table on disk. This was, you can
truncate a table and remove all space associated with the table,
shrinking the space until the table begins accepting rows again.
WARNING: The Oracle
TRUNCATE statement is a DLL command and cannot be rolled back.
The Oracle docs note the syntax for the Oracle TRUNCATE statement as
follows:
TRUNCATE
{ TABLE [ schema. ]table
[ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
| CLUSTER [ schema. ]cluster
}
[ { DROP | REUSE } STORAGE ]
The Oracle TRUNCATE statement is faster than the DELETE command because
it is a DDL command and generates no rollback data. When using Oracle
TRUNCATE on a clustered table, the data must be removed from the entire
cluster, not just the one table. Any referential integrity constraints
on a table must be disabled before it can be truncated. Like a table
DROP, a truncation is not recoverable. If a table is truncated, you
cannot roll back if you made a mistake. Use Oracle TRUNCATE carefully.
The database does not actually
delete any rows with the Oracle TRUNCATE statement, but with the Oracle
TRUNCATE resets the table definition to an empty table by
resetting the high water mark. Because of the nature of the Oracle
TRUNCATE statement, the database has no way to actually retrieve the old
rows. As the truncated table begins to grow, the space where the old
rows were written will be overwritten with the new rows.
