Oracle 11g 閃回查詢

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--確定系統當前SCN

sys@TESTDB11>select current_scn from v$database;

CURRENT_SCN

-----------

    2530398

1 row selected.

--更新表

scott@TESTDB11>update dept set loc='LocD' where deptno = 40;

1 row updated.

scott@TESTDB11>commit;

Commit complete.       

--使用SCN檢視資料的歷史狀態(使用undo資料)

scott@TESTDB11>select * from dept as of scn 2530398;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--使用時間檢視資料的歷史狀態

scott@TESTDB11>select * from dept as of timestamp (systimestamp - 5/(24*60));

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     LocD

scott@TESTDB11>select * from dept as of timestamp (systimestamp - 10/(24*60));

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--使用時間間隔來指定時間

scott@TESTDB11>select * from dept as of timestamp (systimestamp - interval '150' minute);

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON