--当前数据

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