What is the difference between delete and truncate of a table while removing data?
meda Changed status to publish 07/03/2022
When we truncate a table the data can not be rolled back, the statement will be committed automatically. But when we delete a table it can be committed or rolled back.
DELETE is a DML statement but TRUNCATE is a DDL statement.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | SQL> conn mereba/mereba@orclConnected.SQL> insert into sttms_cust_account(ac_desc) values('Messi Dave'); 1 row created.SQL> insert into sttms_cust_account(ac_desc) values('AMAN MULU'); 1 row created.SQL> select * from sttms_cust_account; CUST_NO AC_DESC---------- ------------------------------ 2 Messi Dave 3 AMAN MULU--delete a table dataSQL> DELETE FROM sttms_cust_account; 2 rows deleted.--roll back itSQL> ROLLBACK; Rollback complete.SQL> select * from sttms_cust_account; CUST_NO AC_DESC---------- ------------------------------ 2 Messi Dave 3 AMAN MULU--delete table dataSQL> DELETE FROM sttms_cust_account; 2 rows deleted.--commit itSQL> commit; ----permanent change Commit complete.SQL> select * from sttms_cust_account; no rows selected---truncate itSQL> TRUNCATE TABLE sttms_cust_account; Table truncated.SQL> select * from sttms_cust_account; no rows selectedSQL> |
meda Edited answer 21/02/2022