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).


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.

| improve this answer | |
  • This I know but I am asking that then why DROP is DDL then if it is not acting as autocommited ? – Paresh Zawar Aug 18 '15 at 9:01
  • @PareshZawar For DROP, database has added another layer of recovery, albeit it is a DDL command. – Amnesh Goel Aug 18 '15 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.