Understand Flashback Table in Oracle to Recover Deleted Data

In our workshop, my colleague deleted more than 10,000 rows. And he then commits the changes. After some time, he identified the deleted rows mistakenly. Finally, using the flashback Oracle concept, he did recover the data.

Table of contents

1. What is Flashback Table in Oracle?
2. The architecture of Flashback Table
3. SQL Query to recover data
4. Conclusion

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.

With flashback features, you can:

• Perform queries that return past data.
• Perform queries that return metadata (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 transactions and their dependent transactions while the database remains online.

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

To execute the query, you need privileges.

Conclusion

In the event of accidental data deletion or changes, Oracle’s Flashback Table feature proves to be a powerful tool for recovering lost or mistakenly modified data.

References

• Oracle Flash Back Query
• Flashback Query Example
• What is Oracle Flashback Query