Use of DROP, TRUNCATE, DELETE, FLASHBACK, PURGE

Hi,

In this post, we will discuss about how the update / delete on table works with data. Then we will focus on Drop, Truncate, Purge and Flashback commands. 

When you update or delete the data from a table, the database records the before images of the changes (ie., the image of the data as is) to the rollback segments and all the after image changes (ie., the image of the data after the changes to the data is done) will be recorded to the redo log buffer. 

So, when the commit happens, no changes would be done to the rollback segment, but the redo log buffer is cleared and the actual changed data would be written to the disk. 

Now lets discuss about the other mentioned commands - Drop, Truncate, Purge and Flashback. 

Let's first create a sample table with one column and populate sample data as displayed below. 

select * from b;

When we use TRUNCATE command on table 'b', all the data in table 'b' would be deleted and an internal commit operation is performed as TRUNCATE is a DDL command. But when we use DELETE command on a table, since data is written to the rollback and redo log segments, we would still be able to recover the data deleted using ROLLBACK command but only if you have not used COMMIT command after deleting the data. 

Let's again populate the data as shown in the figure above. 

Now, if we use DROP command on table 'b', the table would be dropped. What actually happens is that this table is not completely dropped. When you query Recyclebin, you would still see the trace of this table. 

You can use either of these statements : 

select * from recyclebin;
or 
select * from user_recyclebin;

So, if an user accidentally dropped a table, they can still recover it. We use FLASHBACK command to achieve this. 

FLASHBACK TABLE b TO BEFORE DROP;

When you do this, the table would be restored to the same state as it was earlier including the data. 

But, if you want to delete the data even from recyclebin once you drop the table, you can use this command:

PURGE recyclebin;

If you want to drop the table explicitly in a way you won't be able to recover, you need to use PURGE command while dropping the table. 

DROP TABLE B PURGE;

Hope you understood the concepts explained in this post. 

Primary Difference between Delete and Truncate : 

Delete is a DML command. Truncate is a DDL command.
In Delete statement we can use where clause But we can’t use where clause in truncate statement.
Delete activates trigger. Truncate does not activate trigger.
We can rollback delete command. We can not rollback truncate command. Delete does not reset identity of table. Truncate resets identity of table.