Difference between DROP,Truncate and Delete in Oracle?

Difference between Truncate and Delete in Oracle?

Answer:

1.TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

2. DELETE is a DML command and can be rolled back.

3. TRUNCATE : You can't use WHERE clause and DELETE : You can use WHERE clause

4. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

5.Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.Delete: You can keep object's statistics and all allocated space.

6. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

7. Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.

8. Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

9. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

10. Truncate will not use the undo TBS, whereas a delete will.