Truncate command Oracle

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

One response to “Truncate command Oracle”

  1. But Truncate command has used less by every one. Delete command used so much by every one. That doesn’t mean truncate is not so important. Nice.