Tag Archives: ORA-30052: invalid lower limit snapshot expression

Flashback Version Query (Recover update/insert statements)

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

VERSIONS_STARTTIME    VERSIONS_ENDTIME VERSIONS_XID     V     SALARY
--------------------- ---------------- ---------------- ----- ---------
06-SEP-18 10.19.19 AM                  03001E001D030000 U     18000
06-SEP-18 10.19.19 AM                                         24000


Note: V= U means update, D means delete, I mmeans insert operation

Alternative method to check query with help of timestamp:

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') WHERE EMPLOYEE_ID = 100;

EMPLOYEE_ID SALARY VERSIONS_STARTTIME    VERSIONS_ENDTIME
----------- ------ --------------------- -----------------
        100 18000  06-SEP-18 10.19.19 AM 
        100 24000  06-SEP-18 10.19.19 AM


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');

Advertisements