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.

With flashback features, you can:

Perform queries that return past data
Perform queries that return metadata that shows a detailed history of changes to the database
Recover tables or rows to a previous point in time
Automatically track and archive transactional data changes
Rollback a transaction and its dependent transactions while the database remains online

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';

References & more examples

How to Build and Deploy Code in AWS, You need These 4 Services

Organizations need speed deployment at scale. AWS provides a service called code pipeline, which is a four-step process explained here.
How to change Date Format in SQL? These are Helpful Functions

Date conversion in SQL simplifies your work, and you will get the desired result. These two functions most people use in the production code.
Hadoop Adaptive MapReduce Points to Read now

The performance and the approach of Adaptive MapReduce in Hadoop explained.