ora-01466

ora-01466

ORA-01466: unable to read data - table definition has changed
Cause: Query parsed after tbl (or index) change, and executed w/old snapshot
Action: commit (or rollback) transaction, and re-execute

SQL> select dbms_flashback.get_system_change_number from dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2110467
 
SQL> update test a set a.dummy = 'z' ;
 
1 row updated
 
SQL> commit;
 
Commit complete

SQL> flashback table test to scn 2110467;
 
Done
 
SQL> select * from test;
 
DUMMY
-----
y

..........

SQL> select * from test VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
 
DUMMY
-----
1
y
z
z
y
 
SQL>

 
 
SQL> select timestamp_to_scn(to_timestamp('2009-12-3 16:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
 
TIMESTAMP_TO_SCN(TO_TIMESTAMP(
------------------------------
                       1972930
 
SQL> flashback table test to scn timestamp_to_scn(to_timestamp('2009-12-3 16:00:00','yyyy-mm-dd hh24:mi:ss'));
 
flashback table test to scn timestamp_to_scn(to_timestamp('2009-12-3 16:00:00','yyyy-mm-dd hh24:mi:ss'))
 
ORA-01466: unable to read data - table definition has changed

SQL> select VERSIONS_OPERATION, VERSIONS_STARTTIME, VERSIONS_ENDTIME,dummy from test  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
 
VERSIONS_OPERATION VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 DUMMY
------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----
                                                                                                                                                                                     1
 
SQL> select * from test VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
 
DUMMY
-----
1
 
SQL> update test set dummy = '2';
 
1 row updated
 
SQL> commit
  2  ;
 
Commit complete
 
SQL> select VERSIONS_OPERATION, VERSIONS_STARTTIME, VERSIONS_ENDTIME,dummy from test  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
 
VERSIONS_OPERATION VERSIONS_STARTTIME                                                               VERSIONS_ENDTIME                                                                 DUMMY
------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----
U                  05-12月-09 08.20.57 上午                                                                                                                                          2
                                                                                                    05-12月-09 08.20.57 上午                                                         1
 
SQL>  flashback table test to scn timestamp_to_scn(to_timestamp('2009-12-5 08:20:55','yyyy-mm-dd hh24:mi:ss'));
 
Done
 

看看系统时间就发现问题了：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
 
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2009-12-05 08:23:01

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-621586/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-621586/