Prerequisites for Flashback recovery:

• Must have FLASHBACK ANY TABLE system privilege or must have FLASHBACK object privilege on the table.
• Must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
• Row movement must be enabled on the table.
  SQL>ALTER TABLE ENABLE ROW MOVEMENT;

Flashback Drop:( Reverses the effects of a DROP TABLE statement)

Reverse the Drop table ->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;

Assign a new name to the restored table->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO ;

Flashback Table: (Reverses a table to its state at a previous point in time)

Case:

At 01:00 PM , we discovers that some rows/records is missing from the EMP table. This record was present at 11:00 AM. Someone accidentally deleted the record from EMP table between 11:00 AM to 01.00 PM. We can return the table to it’s stat at 11.00 AM by Flashback table.

Example:

FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP('2009-01-02 14:14:13','YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS;

OR

FLASHBACK TABLE EMP TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Flashback Query: (We can specify a target time and then run queries, viewing results and recover from an unwanted change)

Case:

At 02:00 PM, we discover that some records deleted from the EMP table and we know that at 9:30AM that data was correctly stored in the database, We could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Example:

Retrives the state of the record at 9:30AM.

SQL> SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2009-01-01 09:30:00', 'YYYY-MM-DD HH:MI:SS');

Restoring information to the table EMP (Missing Data)

INSERT INTO emp (SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2009-01-01 09:30:00', 'YYYY-MM-DD HH:MI:SS');

Flashback Transaction Query: (We can view changes made by a transaction during a period of time.)

Case:

At 03:00 PM, we discover that some records deleted from the EMP table and we know that at 12:00 PM that data was correctly stored in the database, We could query the contents of the table data between 12:00 PM and 03:00PM

SQL>

SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY WHERE xid IN (SELECT versions_xid FROM emp VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS') and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS') );

Flashback database: (We can revert database at a past time)

FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');