FlashBack Using As of Timestamp

What is "As of TimeStamp" and when do you use it? As of time stamp will give you the query result for that particular time. There are times we need to run query as of timestamp mostly when the data are deleted or update by accident. Of course, there are some configuration involved for flashback feature in Oracle to work. The database should be on Archive Log Mode. This great feature is a life savor for DBA and developers, therefore anyone who uses or work with Oracle Database should know this great feature.

Let's say, you have modified a Synonym to point to table_b as per the request. Next day, you got a request to rollback the change you made on  the Synonym. Now, do you have the backup of what it was pointed to, if you do, that's great or else use the query below to generate the DDL as of timestamp.

SELECT * 
FROM   dba_synonyms 
WHERE  synonym_name LIKE 'EMPLOYEE' as OF timestamp SYSDATE -1;

This result of the query will display the query as of  yesterday. Similarly, you can use "as of time stamp" for other Oracle database object types too. Either a DBA or SYS user has the ability to run the query using as of time stamp.

Examples from Oracle Documents 

Example:  Retrieving a lost row with Oracle Flashback Query

SELECT * 
FROM   employees AS OF timestamp To_timestamp('2004-04-04 09:30:00', 
       'YYYY-MM-DD HH:MI:SS') 
WHERE  last_name = 'Chung'; 

Example:  Restoring Chung's information to the employees table:

INSERT INTO employees 
(SELECT * 
 FROM   employees AS OF timestamp To_timestamp('2004-04-04 09:30:00', 
        'YYYY-MM-DD HH:MI:SS') 
 WHERE  last_name = 'Chung'); 

How to check your log mode?

Flashback only works when your database is running in Archive log Mode.  To check the mode you type "archive log list".