FLASHBACK - How to get deleted records after commit & recover dropped tables

Accidentally or by human error, we might delete/commit,  truncate  some records  and drop table which we were not supposed to do.

Have you done this ?  No worries, we can recover them using FLASHBACK feature in Oracle. 

Let us see in detail below. 

How to Recover Deleted Records using AS OF TIMESTAMP :-

Imagine, if you had deleted some records ( and did commit ) from the table SHIPMENT. Here, we go to get back those records with the below query. 

select * from SHIPMENT as of timestamp sysdate-1/24 ;

will give you result of how the table was before 1 hour with the records deleted an hour before.

[or]

select * from SHIPMENT AS OF TIMESTAMP 
      TO_TIMESTAMP('2017-03-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');

will give you the result of how the table was exactly to the particular time.

[or]

insert into SHIPMENT(select * from emp as of timestamp sysdate-1/24);

will insert the deleted records back.

How to recover dropped table:

From Oracle 10g, Recycle bin stores dropped table in other name, which can be recovered later. 

Imagine SHIPMENT_STATUS  table is dropped. Now, the below statement help you to recover the table back as it was before drop.

FLASHBACK table SHIPMENT_STATUS to before drop;

We can even recover the dropped table with new table name. 

FLASHBACK table SHIPMENT_STATUS to before drop rename to SHIPMENT_STATUS_BKP;


Below are the some of the key points to understand about recovering the data and table :-


1. Flashback feature depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 6 hours then. Oracle will not overwrite the data in undo tablespace even after committing until 6 Hours have passed. Users can recover from their mistakes made since last 6 hours only.

To check for the current value,  the below query will revert you the answer in seconds.

SELECT TUNED_UNDORETENTION FROM V$UNDOSTAT ;

2. Recycle bin space is refreshed by Oracle when it need space to store newly impacted tables. Hence, it is not sure the dropped table long time back will be available in Recycle bin for sure. 

SHOW RECYCLEBIN;    query will help you to list the available tables. 

3. When we recover the dropped table, all the indexes , triggers and constraints associated with the table will also be recovered, but BITMAP index wont. Since BITMAP indexes are not being  stored in recycle bin when we drop the table. 

4. If the table is altered using DDL statements like adding a new column, data type change of a column, then deleted records cannot be retrieved. 

I wish not to use FLASHBACK in real time :)