oracle舊數據閃回查詢-CSDN博客

本文通过SQL实例展示了删除、插入数据及使用闪回版本查询的技巧，并提供了相关语法示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

14:01:10 SQL> select * from aa;

        ID NAME                                     BORN
---------- ---------------------------------------- --------------
         1 lishaoqing                               01-10る-11
         1 lishaoqing                               01-11る-11
         1 lishaoqing                               01-12る-11
         1 lishaoqing                               01-1る -12

14:01:15 SQL> show user;
USER ? "SYS"
14:01:19 SQL> delete from aa;

已刪除4個資料列

14:02:36 SQL> select * from aa as of timestamp to_timestamp('20120201 14:01:32','YYYYMMDD HH:MI:SS');
select * from aa as of timestamp to_timestamp('20120201 14:01:32','YYYYMMDD HH:MI:SS')
                                              *
ERROR 在行:1
ORA-01849:小時的數字須介于 1和 12之間

14:03:04 SQL> select * from aa as of timestamp to_timestamp('20120201 02:01:32','YYYYMMDD HH:MI:SS');

        ID NAME                                     BORN
---------- ---------------------------------------- --------------
         1 lishaoqing                               01-10る-11
         1 lishaoqing                               01-11る-11
         1 lishaoqing                               01-12る-11
         1 lishaoqing                               01-1る -12

14:03:19 SQL> insert into aa select * from aa as of timestamp to_timestamp('20120201 02:01:32','YYYYMMDD HH:MI:SS');

已建立4個資料列

14:03:54 SQL> select * from aa;

        ID NAME                                     BORN
---------- ---------------------------------------- --------------
         1 lishaoqing                               01-10月-11
         1 lishaoqing                               01-11月-11
         1 lishaoqing                               01-12月-11
         1 lishaoqing                               01-1月-12

總結：閃回版本查詢的語法如下：
SQL>select [pseudocolumns] ... /*列名
             from ....                                 /*表名
             versions between
              {scn|timestamp {expr|minvalue} and {expr|maxvalue}}
           [AS OF{scn|timestamp expr}]
            where .............
SQL>FLASHBACK TABLE
          [schema.].table
          [,[schema].table ....
          TO {{SCN|TIMESTAMP} expr[{ENABLE|DISABLE} TRIGGERS]
           |BEFORE DROP[RENAME TO table]
           };

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

转载于:http://blog.itpub.net/16381228/viewspace-715424/