通过dbms_flashback包来进行闪回

sys schema :
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     527262

SQL> alter session set current_schema=SCOTT;

Session altered.

SQL> insert into dept values(60,'DMS','CHINA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            CHN
        60 DMS            CHINA
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

6 rows selected.

SQL> desc dbms_flashback
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY_SCN                      NUMBER                  IN
PROCEDURE ENABLE_AT_TIME
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY_TIME                     TIMESTAMP               IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

scott schema:

--设置闪回的SCN

SQL> exec dbms_flashback.enable_at_system_change_number(527262);

PL/SQL procedure successfully completed.
--设置闪回的过去SCN后,只查到5条记录

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            CHN
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
--禁用闪回又查到了当前6条记录

SQL> exec dbms_flashback.disable();

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            CHN
        60 DMS            CHINA
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

6 rows selected.