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