Oracle Flashback Drop — Tips, How-to, and examples

In case you missed the first part of my blog on How to Enable Oracle Flashback you can Click Here.

In this part, I will talk about Oracle Flashback Technology and provide you with examples. So Ali, what exactly is the Oracle Flashback feature?

According to Oracle’s Database Advanced Application Developer’s Guide https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1008

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

With flashback features, you can do the following:

• 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”

Ali, are there any limitations to Oracle Flashback?

Of course, certain limitations will be discussed in detail in this guide and know that you as a developer need to have access to be able to perform these operations. I have intentionally skipped out Flashback Database option either using RMAN or otherwise as it strictly falls under the scope of Database Administrator and should not be done by an individual or a JDA WMS Developer without having the knowledge and associated risks.

EXAMPLES OF USING FLASHBACK

1. Flashback Drop
2. Flashback Table

Flashback DROP

In this example, we will create a table ctnmst_bk_20191116 with only data for WMD1 warehouse and then drop it. We will then recreate it without any filter. We will use the flashback query to recreate the table through a point in time recovery.

For our example, we will create table ctnmst_bk_20191116 from ctnmst using the following query:

Total of 47 rows of data in new ctnmst_bk_20191116

Table ctnmst_bk_20191116 dropped and recreated without any filter:

Total of 85 rows of data in new ctnmst_bk_20191116

Number of Rows for each warehouse:

Now let’s say we “accidentally” deleted this table:

You can use user_recyclebin oracle view to find the dropped object since the dropped objects are sent to recycle bin instead of purging because of the flashback feature being enabled:

After using the SQL Statement below we can restore the table to its previous state before it was dropped:

[flashback table ctnmst_bk_20191116 to before drop];
---OR---
[FLASHBACK TABLE "BIN$l3kz8mXrFhLgUzoICgrKig==$0" TO BEFORE DROP];

Note: Please do remember if there are multiple rows against the same table, the flashback table tablename to before drop will restore the most recently dropped table. So, it’s always best to view the user_recyclebin before restoring the table.

That’s it! — The table is restored successfully after being “accidentally dropped”.

Considerations:

1. If a table is purged using the purge clause, then it is not sent to the recycle bin and is purged permanently, making it unrecoverable.

[drop table ctnmst_bk_20191116 purge];

2. If the recycle bin is purged, all objects within the user/schema are unrecoverable. Similarly purge dba_recyclebin; removes all objects belonging to all schemas from the recycle bin.

purge recyclebin;

Click here to read the next part of this story on Oracle Flashback where I will discuss on the following:

1. Flashback Tables using SCN (System Change Number)
2. Flashback Tables using Timestamp
3. Recover accidentally overwritten Values
4. Recover deleted data via an insert statement