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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
Scroll to Top