ORACLE FLASHBACK的另类应用

select    tab_log_ver
as select *
    from tab_log as timestamp('2008-12-08 8:00:00', 'YYYY-MM-DD HH24:MI:SS') where rownum<5;

首先创建一张测试表：
SQL> desc test_log_ver
Name                Type                    Nullable Default Comments 
----------- ------------- -------- ------- -------- 
MESSAGE_ID    VARCHAR2(100)                                                     
TASK_ID         VARCHAR2(18)                                                        
SUBMIT_TIME VARCHAR2(14)    Y                                                 
TERMINAL_ID VARCHAR2(20)    Y                                                 
STATUS            CHAR(10)            Y

SQL> alter table test_log_ver modify (MESSAGE_ID varchar2(30));

Table altered

Executed in 0.125 seconds

SQL> alter table test_log_ver add(test_col number);

Table altered

Executed in 0 seconds

SQL> select object_name,
    2                 to_char(created, 'yyyy-mm-dd hh24:mi:ss') created,
    3                 to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') last_ddl_time
    4        from user_objects
    5     where object_name = 'TEST_LOG_VER'
    6    ;

OBJECT_NAME      CREATED              LAST_DDL_TIME
---------------- -------------------  ---------------------
TEST_LOG_VER     2008-12-08 15:38:00  2008-12-08 15:46:15

Executed in 0.016 seconds

select * from test_log_ver as of timestamp
to_timestamp('2008-12-08 15:38:10', 'YYYY-MM-DD HH24:MI:SS')

ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') SYSDATE from dual;

SYSDATE
-----------------------
2008-12-08 16:28:50

在16:28分时，执行FLASHBACK操作，并且指定的时间大于2008-12-08 15:46:15。

SQL> flashback    table XXX.test_log_ver to timestamp to_timestamp('2008-12-08 15:50:00', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.
成功执行！