What is the Difference between Truncate and Delete in Oracle

Difference Between Truncate and Delete

First create a Table using following command on SQL> prompt.

CREATE TABLE SAMPLE (ID NUMBER, NAME VARCHAR2(20),CITY VARCHAR2(15), ADDRESS VARCHAR2(50));

Now insert three records into SAMPLE table using following command.

INSERT INTO SAMPLE VALUES (1,’AMIT KUMAR’,’DELHI’,’MAIN MARKET, DELHI’);


INSERT INTO SAMPLE VALUES (2,’SUDHIR MALHOTRA’,’AGRA’,’ALIBAGH’);

INSERT INTO SAMPLE VALUES (3,’DEVANG MEHRA’,’ALIGARH’,’NEAR RLY. ST.’);


Don’t forget to commit after inserting values into SAMPLE table.

COMMIT;

Explore the difference between DELETE, TRUNCATE and DROP command in Oracle.

DELETE Command:

The DELETE command is used to delete/remove rows from a Table. A WHERE clause can be used to delete some of the rows/records from a Table. If WHERE clause is not specified, all rows will be removed from a Table.

When you perform a DELETE operation, you need to COMMIT or ROLLBACK the transaction to make the changes permanent or to undo it.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

3

SQL> DELETE FROM SAMPLE;

3 rows deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

3

SQL> DELETE FROM SAMPLE;

3 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

0

With WHERE Clause:

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

3

SQL> DELETE FROM SAMPLE WHERE ID=2;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

2

TRUNCATE Command:

TRUNCATE command removes all rows from a Table. The operation can’t be rolled back. It means if you fire TRUNCATE command on any table then the records/rows can’t be rolled back.

TRUNCATE command is much faster than DELETE command because it doesn’t use as much undo space as DELETE.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

3

SQL> TRUNCATE TABLE SAMPLE;

Table truncated.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

0

I hope you understood the difference between truncate and delete. Now check the use of Drop command.

DROP Command:

This command’s function is entirely different from both the above command.

The DROP command removes a Table from the Database. When you drop a Table from database then all the table rows, indexes, privileges also gets removed from database.

This operation also can’t be rolled back like TRUNCATE operation.

SQL> SELECT COUNT(*) FROM SAMPLE;

COUNT(*)

———-

3

SQL> DROP TABLE SAMPLE;

Table dropped.

SQL> SELECT COUNT(*) FROM SAMPLE;

SELECT COUNT(*) FROM SAMPLE

*

ERROR at line 1:

ORA-00942: table or view does not exist

NOTE: DELETE is a DML command whereas TRUNCATE and DROP are DDL commands. Therefore DELETE operations can be rolled back, while TRUNCATE and DROP operation can’t be rolled back.

NOTE: Since Oracle 10g a Table can be undropped.

SQL> FLASHBACK TABLE emp to BEFORE DROP;

Flashback Complete.

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
60% OFF SiteGround WordPress Hosting – $3.95/month (Exclusive Offer !!!)