Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

Question

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider backup).

Answer 1

We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table.

So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of that table and it maintains an entry of that table (in other words, sort of a soft delete operation). When you PURGE, then only database start looking for those table entries which are marked as soft delete and start deleting them permanently. Hence when it is purge (or permanently deleted) then you cannot recover it.

OTOH when you execute a TRUNCATION query, then it deletes the data but it is also combined with PURGE command. So when you TRUNCATE then it removes the data permanently.

In simple words, you can relate this with example of DEL and Shift+Del. When you DEL a file, then it goes to RecycleBin and you can recover it from there. But when you delete a file with Shift+Del then it permanently deletes. I hope it helps.

Answer 2

You can revert both operations - DROP TABLE and TRUNCATE TABLE - in Oracle. Though both are autocommited.

Oracle uses different mechanisms in order to do that.

DROP TABLE is based on RECYCLEBIN feature. So the table is not really dropped. It is just moved to RECYCLEBIN. Just like files in Windows. You can take table from RECYCLEBIN with one command.

TRUNCATE on the other hand does not drop the table so there is nothing to move. It is just an operation on dictionary. It frees storage. The table definition still exists.

You can access truncated data with query similar to this:

SELECT * FROM truncated_table as of timestamp timestamp'yyyy-mm-dd hh24:mi:ss'

where 'yyyy-mm-dd hh24:mi:ss' is a date from before truncate.

One condition: it is based on UNDO. So the time in past you can get data from depends on the UNDO size and number of changes in the database.

You can read data from past without truncating the table before.