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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/175005/viewspace-293573/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/175005/viewspace-293573/