How to rollback deleted records after commit in Oracle

Introduction
Summary
Next Steps

Introduction

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.

Summary

Do you think it’s impossible to rollback your changes after you commit a Delete command? Well, it’s possible.

Next Steps

If you're interested in learning more about the basics of Java, coding, and software development, check out our Coding Essentials Guidebook for Developers, where we cover the essential languages, concepts, and tools that you'll need to become a professional developer.

Thanks and happy coding! We hope you enjoyed this article. If you have any questions or comments, feel free to reach out to jacob@initialcommit.io.