Truncate command is a DDL command to delete all records from a table. Since it is a DDL command you cannot rollback after Truncate.


Truncate table table_name [ DROP|REUSE storage ];

Example 1 –  :-

SQL> desc test;
Name                                      Null?    Type
—————————————– ——– —————————-
N                                                  NUMBER
NAME                                         VARCHAR2(50)
D                                                  DATE

SQL> truncate table test drop storage;

Table truncated.

( Note that truncate table test drop storage and truncate table test are same since drop storage clause is the default )

In above case Oracle reset the high water mark.

Example 2 – Truncate with reuse storage :-

SQL> desc LOG;
Name                                      Null?    Type
—————————————– ——– ————-
N                                         NOT NULL NUMBER(38)
MYTEXT                                                VARCHAR2(100)
D                                                              TIMESTAMP(6)

SQL> truncate table log reuse storage;

Table truncated.

In this  case Oracle will not reset the high water mark and same storage space will allocated for new entries in future.

Restrictions on Truncate

1. Cannot truncate a parent table with a foreign key constraint

2. Cannot truncate a table that is part of a cluster.

Generally Truncate is much faster than Delete command. But there are some major differences. Read  Difference between Truncate and Delete