Fix ORA-30052: invalid lower limit snapshot expression

advertisements

_____________________________________________________________________________________________________________________

Error Description:
Oracle flashback transaction version query failing with following error message.
SQL> SELECT *
FROM dept
VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2014-07-14 14:12:00')
  AND TO_TIMESTAMP('2014-07-14 14:26:01')   ;
FROM dept
     *
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression
 

Solution Description:

Check your UNDO_RETENTION parameter value. In flashback version query you can query only during the transaction between the undo retention period. See an example below.

SQL>show parameter undo_retention
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
undo_retention                       integer     900
 
Undo retention is 15 minutes and the system time is 2:26PM. So the first query won’t work because the time period is more than undo retention. The second query will work as the time period is within the undo retention.

SQL> !date

Mon Jul 14 14:26:32 CEST 2014
 
SQL> SELECT *
FROM dept
VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2014-07-14 14:10:00')
  AND TO_TIMESTAMP('2014-07-14 14:26:01')   ;
FROM dept
     *
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression
 
SQL> SELECT *
FROM dept
VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2014-07-14 14:12:00')
  AND TO_TIMESTAMP('2014-07-14 14:26:01')   ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

_____________________________________________________________________________________________________________________

Website Stats