How to rollback deleted records after commit in Oracle
Do you think it’s impossible to rollback your changes after you commit a Delete command? Well, it’s possible.
In version 11g, Oracle keeps snapshots of your table for some time and allows you to rollback to a particular snapshot as long as this period of time is not passed.
This is the command that retrieves a snapshot of your table at a specific time:
1 | Select * From TABLE_NAME as of timestamp(sysdate - 10/1440)); |
In the above command, we retrieve a snapshot of our table as of 10 minutes ago. The “10” refers to the number of minutes back from now, and “1440” refers to the number of minutes of a day.
Suppose you delete and commit some records by mistake at a production environment. In order to revert back your changes, simply create a temporary table from a “5 mins ago” snapshot:
1 2 | create table TABLE_NAME_5MINS AS Select * From TABLE_NAME as of timestamp(sysdate - 5/1440)); |
Then, insert back the deleted records to the main table.
Leave a Reply