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:
SQL> conn mereba/mereba@orcl Connected. 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 data SQL> DELETE FROM sttms_cust_account; 2 rows deleted. --roll back it SQL> ROLLBACK; Rollback complete. SQL> select * from sttms_cust_account; CUST_NO AC_DESC ---------- ------------------------------ 2 Messi Dave 3 AMAN MULU --delete table data SQL> DELETE FROM sttms_cust_account; 2 rows deleted. --commit it SQL> commit; ----permanent change Commit complete. SQL> select * from sttms_cust_account; no rows selected ---truncate it SQL> TRUNCATE TABLE sttms_cust_account; Table truncated. SQL> select * from sttms_cust_account; no rows selected SQL>
meda Edited answer 21/02/2022