Flashback Version Query (Recover update/insert statements)

With help of flashback version query, we can check the value of particular column at specified timing it also depend upon the undo segments as long as it available in undo.
Note: its depend upto undo_retention parameter value, if it is 900 thats means 15 min, you can check this query within 15 min of server data.

In an example, we will show how it help to recover the data which is wrongly updated and don’t have previous record without full restore of database.

Suppose we have employee salary column in salary table that is updated wrongly but we need to roll-back it then we have to know the previous value of salary column for restore.
Other wise we have to restore completed backup at other location and copy that table to production environment

Example
This is our salary column in employees table we have to modified the salary of LEX (id 102) but by mistake it modified for Steven (id 100)

SQL> select employee_id,first_name,salary from employees;
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
100 Steven 24000
101 Neena 17000
102 Lex 17000
103 Alexander 9000


Updated the STEVEN salary instead of LEX salary

SQL> update employees set salary=18000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.


Check the salary update and got it wrongly updated

SQL> select employee_id,first_name,salary from employees;
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
100 Steven 18000
101 Neena 17000
102 Lex 17000
103 Alexander 9000
On checking we modified the salary of wrong employee, we need to recover it.

Now with help of Flashback version Query we will tried to get old value of column
SELECT versions_starttime, versions_endtime, versions_xid, versions_operation, salary
FROM employees versions BETWEEN timestamp minvalue AND maxvalue where employee_id = 100 ORDER BY VERSIONS_STARTTIME
Note: If you donot use where clause then it will return all tables rows

You can check the Query as follows with Version_xid value
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '03001E001D030000';

Error: ORA-30052: invalid lower limit snapshot expression
SQL> SELECT EMPLOYEE_ID, salary, versions_starttime, versions_endtime
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:00:00','mm/dd/yyyy hh24:mi:ss')
AND TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss') ;
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:30:00','mm/dd/yyyy hh24:mi:ss') AND
TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression
Solution:
It depend upon undo retention value. If Undo retention is 15 minutes and the system time is 10:30 AM. So the first query won’t work because the time period is more than undo retention.
Following query will work if you change the time limit in first parameter successfully.
SELECT EMPLOYEE_ID, salary, versions_starttime, versions_endtime
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:18:00','mm/dd/yyyy hh24:mi:ss') AND TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss');