Oracle 11g 閃回表

--當前資料

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     LocG

--在SCN=2530398時的歷史資料

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

--使表資料閃回到scn 2530398;

scott@TESTDB11>flashback table dept to scn 2530398;

flashback table dept to scn 2530398

                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

--允許在資料移動的時候更改rowid

scott@TESTDB11>alter table dept enable row movement;

Table altered.

scott@TESTDB11>flashback table dept to scn 2530398;

flashback table dept to scn 2530398

                *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-25153: Temporary Tablespace is Empty

--確定SCOTT使用者使用的臨時表空間

sys@TESTDB11>select temporary_tablespace from dba_users where username = 'SCOTT';

TEMPORARY_TABLESPACE

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

TEMP

sys@TESTDB11>alter tablespace temp add tempfile '/oradata/temp01.dbf' size  500m;

Tablespace altered.

scott@TESTDB11>flashback table dept to scn 2530398;

Flashback complete.

--閃回到最早的狀態

scott@TESTDB11>select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON