How to Truncate TABLE in Oracle

Truncate Table in Oracle is a useful command. It is used to delete all the rows in the table plus release the space allocated to the table. Here are some important points regarding the Truncate table

• We can use truncate table command to delete all the rows  in the table and it releases all the storage space allocated to the table. It resets the high water mark of the table
• This command cannot be rollback
• You should be owner of the table to execute the operation
• Truncating the table is a DDL command and it does not fire the on delete triggers
• We can delete all the rows in the table by delete command also but it does not release the storage space allocated to  the table
• When you truncate a table, Oracle Database automatically removes all data in the table’s indexes and any materialized view direct-path INSERT information held in association with the table

Truncate table Syntax in Oracle

Truncate table <table name>
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ];

-Where <table name> is the name of the table and you must be the owner of the table or have Drop any TABLE system privileges to truncate a table
-Storage is dropped by default if even not specified. If you want to preserve space, you can keep storage, then reuse storage

If you are truncating the table of another schema, use like this

Truncate table <owner>.<table name>

Examples

Truncate table EMP;
Truncate table SCOTT.EMP;

Truncate table SCOTT.EMP reuse storage;

How to grant truncate table in oracle

There is no truncate table privilege in Oracle. You need to provide Drop any table privilege to grant truncate table in oracle. Drop any table comes with many other privileges. So, this may not be possible in all cases. You can overcome this challenge by creating a procedure and granting execute on that procedure. Let’s understand with the example

Suppose you want to give a truncate table of one user USER1 to another user USER2

If you try to truncate the table simply, then you will hit the Error

conn user2/pass
truncate table user1.EMP
*
ERROR at line 1:
ORA-01031: insufficient privileges

Now let’s try to do this thing through the procedure and granting privilege on it

Conn user1/pass

create or replace procedure trunc_t( p_table in VARCHAR2)
is
v_count pls_integer;
BEGIN
select count(*) into v_count
from user_tables
where
table_name = p_table;
if ( v_count = 1 )
then
execute immediate 'truncate table '|| p_table;
else
raise_application_error( -20001,
'table does not exists' );
end if;
END;
/

grant execute on trunc_t to user2;

Conn user2/pass
exec trunc_t('EMP');

If you don’t want to do this thing, then you will need to grant drop any table privilege

conn system/<pass>
grant drop any table to user2;

Truncate Table Cascade

• Before Oracle 12c, You cannot truncate the parent table of an enabled foreign key constraint. If you try it, you will get ORA-02266. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
• With Oracle 12c R1, Oracle has introduced Cascade clause for Truncate. We have to Specify CASCADE allowing you to recursively truncate down the tables in a hierarchy If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not truncate the table. Lets understand this truncate table with cascade with an example

CREATE TABLE "EMP"
( "EMPNO" NUMBER(6,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ON DELETE CASCADE ENABLE
);

CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
;

SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------

EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)SQL>

SQL> desc dept
Name Null? Type
----------------------------------------- -------- -----------------------

DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL>

insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');

insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );

insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10

7782 CLARK MANAGER 7839 09-JUN-08 2450 10

7788 SCOTT ANALYST 7566 09-JUN-12 3000 20

7789 TPM ANALYST 7566 09-JUN-17 3000

7790 TOM ANALYST 7567 09-JUL-17 4000

4534 xyz 1000 20

4576 abc 1000

7560 T1OM ANALYST 7567 09-JUL-17 4000 20

SQL> truncate  table dept;
truncate table dept
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by foreign keys

SQL>
SQL> truncate table dept cascade;

Table truncated.

It is important to note foreign key constraints should have an ON DELETE CASCADE for this to work. It is an important point to note truncate table with cascade not only deletes data from the DEPT table only but it also deletes the EMP table.

select * from DEPT;
no rows Selected

select * from EMP;
no rows Selected

oracle truncate table vs delete

Truncate	Delete
Delete all the rows from the tables	It can be used to delete one or more rows from a table
DDL commands and does not fire On DELETE triggers	DML Command and fire ON Delete triggers
It resets the Highwater mark in the table	It does not change the high water mark in the table
Cannot be rollback	Can be rollback
Faster	slower
Cannot specify where the clause here	Where clause can be specified
You have the option of keeping or dropping the storage allocated to the segment	It does not have this option. Storage remains same

Hope This post is helpful for a truncate table in oracle

Related Articles

Oracle Create table
check table size in Oracle
oracle show all tables
Delete from a table in Oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm