Oracle flashback query to the rescue

Published by Vignesh M on

In this post, I would like to mention one of the feature of oracle database i.e Oracle flashback query. Recently we as a team involved in the activity of bad data correction. One fine day, for some bug in the PL/SQL code, the good data got deleted as well. Well! Going wrong with the DELETE would happen and we have to overcome it.

Oracle flashback query

With the Oracle flashback query we can specify the as of timestamp and retrieve the database values of the table which has been affected by erroneous update/delete.

For example, we have a table RETRO_T

The above result set is after the deletion of record with retro_type=Active.

The transaction is committed. But we need to retrieve the data. We can execute the oracle flashback query to get them back.

If you know the timestamp of when the data is in correct state, then you can provide the timestamp to the query.

The result set would be as below:

To insert the record back, we could ideally run the below query:

Flashback feature retention target

By default, the flashback retention target is one day (1440 minutes). Based on the need this can be adjusted. To be able to perform 72 hour flashback, we can change the retention target to 4320 minutes as below:

Conclusion

Oracle flashback is one of the powerful feature to overcome those erroneous database operations. Most importantly to overcome quickly!

References

http://www.dba-oracle.com/t_rman_149_flasbback_query.htm

https://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmflash.htm


Vignesh M

Java developer , AWS Certified Solutions Architect Associate and Cloud technology enthusiast. Currently working for Hexaware technologies. He believes that knowledge increases by sharing not by saving.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.