ORA-30052: invalid lower limit snapshot expression

删除了一个表,没记住具体时间,结果闪回版本查询时,出现上面那个错误,原因是我的undo_rentention=900只记录了15分钟,可以能上限选择15分钟之前,或者下限选择15分钟之后.

通过查询flashback_transaction_query查看具体的操作时间

SQL>  select undo_sql ,START_TIMESTAMP,COMMIT_TIMESTAMP from flashback_transaction_query
  2        where table_name='EMP';

UNDO_SQL                                                                         START_TIMESTAMP     COMMIT_TIMESTAMP
-------------------------------------------------------------------------------- ------------------- -------------------
update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAANBIAAEAAAAAkAAL';       2008-05-23 09:55:59 2008-05-23 09:56:17
update "SCOTT"."EMP" set "SAL" = '2950' where ROWID = 'AAANBIAAEAAAAAkAAL';      2008-05-23 09:56:32 2008-05-23 09:56:35
update "SCOTT"."EMP" set "SAL" = '1950' where ROWID = 'AAANBIAAEAAAAAkAAL';      2008-05-23 09:56:23 2008-05-23 09:56:26

修改查询时间,错误不再出现

SQL> conn scott/tiger
已连接。
SQL> select * from emp versions between
  2  timestamp to_timestamp('2008-05-23 09:55:59','yyyy-mm-dd hh24:mi:ss') and
  3  to_timestamp('2008-05-23 09:56:55','yyyy-mm-dd hh24:mi:ss')
  4  where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00       3950                    30
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00       2950                    30
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00       1950                    30
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30