Oracle Flashback Query

--Flashback Technology Allows to let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.Flashback feature depends upon on how much undo retention time you have specified

So.

Let's We take a look about Oracle FlashBack Query & Understand with Test Case

Oracle FlashBack Query

1. Create environment for testing 

SQL> select * from V1 where no<=5;

NO NAME 

---------- --------------- 

1 a661 

2 a662 

3 a663 

4 a664 

5 a665 

SQL> create table flashback_vvs as select * from V1 where no<=5;

Table created.

SQL> select * from FLASHBACK_VVS;

NO NAME 

---------- --------------- 

1 a661 

2 a662 

3 a663 

4 a664 

5 a665 

2. Now assume that wrong update has been commited now we have to go in previous state

SQL> update FLASHBACK_VVS set no=6+ROWNUM;

5 rows updated.

SQL> commit; 

Commit complete.

SQL> select * from FLASHBACK_VVS;

NO NAME 

---------- --------------- 

7 a661 

8 a662 

9 a663 

10 a664 

11 a665 

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM 

----------- ------------------- 

6456362 2018-09-25 13:27:11

3.Now the with use of flashback query we can take previous table state and create a new different table or update with query

SQL> SELECT * FROM FLASHBACK_VVS AS OF TIMESTAMP TO_TIMESTAMP('2018-09-25 13:26:50', 'YYYY-MM-DD HH24:MI:SS');

NO  NAME 

---------- --------------- 

1  a661 

2  a662 

3  a663 

4  a664 

5  a665 

SQL> UPDATE FLASHBACK_VVS B

2 SET NO = (SELECT NO FROM FLASHBACK_VVS AS OF TIMESTAMP TO_TIMESTAMP('2018-09-25 13:26:50', 'YYYY-MM-DD HH24:MI:SS')

3 WHERE NAME=B.NAME);

5 rows updated.

SQL> select * from FLASHBACK_VVS;

NO  NAME 

---------- --------------- 

1  a661 

2  a662 

3  a663 

4  a664 

5  a665 

----IF WANT TO CREATE A NEW TABLE WE CAN ALSO CREATE TABLE AT PREVIOUS TIME STAMP

SQL >create table FLASHBACKED_VVS

AS 

SELECT * FROM FLASHBACK_VVS AS OF TIMESTAMP TO_TIMESTAMP('2018-09-25 13:26:50', 'YYYY-MM-DD HH24:MI:SS');

Thanks,

Stay tune on DBA SNIPER