How to Recover Deleted Data in Oracle

Recently in our workshop, my colleague deleted more than 10,000 rows. And he then commits the changes. After some time, he identified the delete was wrong.

How to recover this data? It’s a big question for him. Usually, after committing, it’s hard to recover data (otherwise restore the data). Finally, from Oracle Flashback Table, he could recover the data.

What is Flashback Table in Oracle?

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or return database objects to a previous state without using point-in-time media recovery.

From Oracle Ver. 9i Oracle has introduced the Flashback Query feature. It is handy to recover from accidental statement failures.

Architecture of Flashback Table

SQL Query to recover data

Here is the example SQL query. You can use it to recover data from the flashback table to a specific point in time.

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

The as of timestamp to execute it, you need privileges’. Here is a solution to resolve the issue.

References & more examples

How to Write Echo Command Correctly in Linux

Here are ideas to write Echo command in Linux shell script.
Linux Sed Command: Way to Get Match Criteria

This topic shows to use the sed command in Linux to get a matching string
How to add Boolean Operators in MySQL Query

An example SQL query that works in MySQL shows how to use Boolean Operators in the WHERE clause.