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.

Syntax:-

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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s