1.首先连接到数据 Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as scott

2.查看待闪回的表中数据： SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

12 rows selected

3.确认当前SCN，创建检查点用于闪回： SQL> select current_scn from v$database;

CURRENT_SCN -----------     4224762

SQL> create restore point test_rp;

Done

SQL> SQL> SELECT NAME, SCN, TIME   2  FROM V$RESTORE_POINT;

NAME                                                                                    SCN TIME -------------------------------------------------------------------------------- ---------- ------------------------------------------------- TEST_RP                                                                             4224767 10-5？  -13 10.16.23.000000000 ？？

4.查看UNDO数据的保留时间，以分钟形式显示： SQL> SQL> SELECT NAME, VALUE/60 MINUTES_RETAINED   2  FROM V$PARAMETER   3  WHERE NAME = 'undo_retention';

NAME                                                                             MINUTES_RETAINED -------------------------------------------------------------------------------- ---------------- undo_retention                                                                                 15

5.允许表的行移动 SQL> ALTER TABLE EMP ENABLE ROW MOVEMENT;

Table altered

6.查看一下待闪回的表与其他表之间是否存在约束关系，考虑是否需要一并闪回 SQL> SQL> SELECT other.owner, other.table_name   2  FROM sys.all_constraints this, sys.all_constraints other   3  WHERE this.owner = 'SCOTT'   4  AND this.table_name ='EMP'   5  AND this.r_owner = other.owner   6  AND this.r_constraint_name = other.constraint_name   7  AND this.constraint_type='R';

OWNER                          TABLE_NAME ------------------------------ ------------------------------ SCOTT                          EMP SCOTT                          DEPT

7.执行一些DML操作

SQL> delete from emp;

12 rows deleted

SQL> commit;

Commit complete

8.闪回到已创建的恢复点 SQL> flashback table scott.emp to restore point test_rp;

Done

9.查看效果 SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

12 rows selected

以上是正常情况的闪回。 如果两个表存在约束，某个表的修改导致另一个表无法闪回，会出现什么现象呢？

SQL> select * from dept;

DEPTNO DNAME          LOC ------ -------------- -------------     10 ACCOUNTING     NEW YORK     20 RESEARCH       DALLAS     30 SALES          CHICAGO     40 OPERATIONS     BOSTON

我们知道，scott模式下的emp与dept存在外键。下面我们对dept和emp进行修改，然后闪回emp表：

SQL> delete from emp where deptno=20;

3 rows deleted

SQL> commit;

Commit complete

SQL> delete from dept where deptno=20;

1 row deleted

SQL> commit;

Commit complete

SQL> flashback table emp to restore point test_rp;

flashback table emp to restore point test_rp

ORA-02091: ？？？？？？？ ORA-02291: ？？？？？？？？ (SCOTT.FK_DEPTNO) - ？？？？？？？？

在另外一个会话中查询该报错： [oracle@localhost ~]$ oerr ora 2091 02091, 00000, "transaction rolled back" // *Cause: Also see error 2092. If the transaction is aborted at a remote //         site then you will only see 2091; if aborted at host then you will //         see 2092 and 2091. // *Action: Add rollback segment and retry the transaction.

回到当前会话，执行： SQL> flashback table dept to restore point test_rp;

flashback table dept to restore point test_rp

ORA-08189: ？？？？？？？？？？, ？？？？？

该报错是由于没有开启row movement导致的。 [oracle@localhost ~]$ oerr ora 8189 08189, 00000, "cannot flashback the table because row movement is not enabled" // *Cause: An attempt was made to perform. Flashback Table operation on a table for //         which row movement has not been enabled. Because the Flashback Table //         does not preserve the rowids, it is necessary that row //         movement be enabled on the table. // *Action: Enable row movement on the table

下面我们开启该特性，并按照依赖关系进行闪回： SQL> alter table dept enable row movement;

Table altered

SQL> flashback table scott.dept to restore point test_rp;

Done

SQL> flashback table emp to restore point test_rp;

Done

闪回过程中，表上的触发器会被变为禁用状态，如果在闪回过程中系统触发器可用，可以在flashback命令上添加enable trigger子句，如： flashback table emp to restore point test_rp enable triggers;