Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-08180: no snapshot found based on specified time

happy10319Apr 2 2010 — edited Apr 2 2010
Hi,
on 10g R2, why I can not use Flash version query even if I use a large time interval :
SQL> SELECT versions_startscn, versions_starttime,
  2         versions_endscn, versions_endtime,
  3         versions_xid, versions_operation,
  4  ename from  scott.EMP
  5  VERSIONS BETWEEN TIMESTAMP
  6        TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7    AND TO_TIMESTAMP('2010-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
  8  ;
ename from  scott.EMP
                  *
ERROR at line 4:
ORA-08180: no snapshot found based on specified time


SQL> select ename, sal from scott.emp;

ENAME             SAL
---------- ----------
SMITH             800

SQL> update scott.emp set SAL=SAL*2 where ename='SMITH';

1 row updated.

SQL> select ename, sal from scott.emp;

ENAME             SAL
---------- ----------
SMITH            1600

SQL> SELECT versions_startscn, versions_starttime, 
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
ename from  scott.EMP
VERSIONS BETWEEN TIMESTAMP 
      TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2010-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
ename from  scott.EMP
                  *
ERROR at line 4:
ORA-08180: no snapshot found based on specified time


SQL> commit;

Commit complete.

SQL> SELECT versions_startscn, versions_starttime,
  2         versions_endscn, versions_endtime,
  3         versions_xid, versions_operation,
  4  ename from  scott.EMP
  5  VERSIONS BETWEEN TIMESTAMP
  6        TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7    AND TO_TIMESTAMP('2010-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
  8  ;
ename from  scott.EMP
                  *
ERROR at line 4:
ORA-08180: no snapshot found based on specified time
Thank you.
PS :
ORA-08180: no snapshot found based on specified time 
Cause: Could not match the time to an SCN from the mapping table.
 
Action: try using a larger time.
 
This post has been answered by orawiss on Apr 2 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 30 2010
Added on Apr 2 2010
12 comments
14,114 views