0

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.

KnockingHeads
  • 1,569
  • 1
  • 22
  • 42
  • Savepoints and rollback won't help you anyway; DDL operations including `drop` [implicitly commit](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-510DF997-BA9C-4431-95EF-A0622881EC9C). Have you looked at [flashback](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FLASHBACK-TABLE.html)? – Alex Poole Aug 04 '20 at 08:58

1 Answers1

5

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>>
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you so much Justin. I tried flashback. Could there be any reason flashback doesn't work? – KnockingHeads Aug 04 '20 at 14:24
  • @Ashish - Which command did you try? `flashback table`? Or `flashback drop`? What does "doesn't work" mean? Did you get an error? If so, what was the error? – Justin Cave Aug 04 '20 at 16:15
  • @Ashish Flashback has to be enabled in your database. We don't enable it do to the additional overhead. – Brian Leach Aug 05 '20 at 00:07
  • 1
    @BrianLeach - "Flashback" is a broad term for a number of different features that are controlled by a number of different settings. You have to enable flashback logs to be able to do a `flashback database`. You do not need to do anything to enable `flashback table`. You can disable the recycle bin which will prevent `flashback table` from working. There are also flashback queries which use `undo` rather than flashback logs so those are configured differently. – Justin Cave Aug 05 '20 at 02:09