How to rollback all the commits in Oracle DB

Question

Recently I made a mistake, that, all the tables in my oracle database were dropped (running drop command from SQL Developer tool) in a sequence of all transactions unbeknownst to me that I was dropping tables at the wrong database. Each time I dropped a set of tables, I committed the operations. By the time, I realized that I have dropped the tables at the wrong DB, I was too late to rollback, since it rolled back the last operation only. I searched online as well, and found SavePoints to be the resolution, but I had no savepoints configured. I had the backup of all the tables taken 2 days before, so, I ran scripts from there and made lost changes for the past 2 days. Is there any other way that I take to get my DB state back. I have made it a practice to make savepoints while performing such operations though. But, I am still confused. One who doesn't know about savepoints will lose all the data.

Answer 1

DDL statements like drop do an implicit commit so you cannot rollback to undrop a table. Nor would a savepoint help you since that only lets you go back to an earlier point in the current transaction.

If you have merely dropped a table, assuming you haven't disabled the recycle bin, you can simply do a flashback drop

flashback table <<table name>> to before drop

If you have a more complicated script that is doing more than just dropping tables, you may want to set a restore point prior to running the script. You could then flashback the entire database to that restore point if something goes wrong.

create restore point <<your restore point name>>

<<run your script>>

flashback database to restore point <<your restore point name>>