Flashback Version Query (Recover update/insert statements)

With the flashback version query, we can check the value of a specific column at a given time, depending on the available undo segments. Note: this depends on the undo_retention parameter value. If it is set to 900, that means 15 minutes; you can check this query within 15 minutes of the server data.

In this example, we will demonstrate how to recover data that was incorrectly updated without needing to fully restore the database.

If the employee salary column in the salary table is updated incorrectly and we need to revert it, we must know the previous salary values. Otherwise, we will need to restore a complete backup from another location and copy that table to the 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 Alex ander 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');