1.首先连接到数据
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
2.查看待闪回的表中数据：
SQL> select * from emp;
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
----- ---------- --------- ----- ----------- --------- --------- ------
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;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4224762
-----------
4224762
SQL> create restore point test_rp;
Done
SQL>
SQL> SELECT NAME, SCN, TIME
2 FROM V$RESTORE_POINT;
SQL> SELECT NAME, SCN, TIME
2 FROM V$RESTORE_POINT;
NAME SCN TIME
-------------------------------------------------------------------------------- ---------- -------------------------------------------------
TEST_RP 4224767 10-5？ -13 10.16.23.000000000 ？？
-------------------------------------------------------------------------------- ---------- -------------------------------------------------
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
-------------------------------------------------------------------------------- ----------------
undo_retention 15
5.允许表的行移动
SQL> ALTER TABLE EMP ENABLE ROW MOVEMENT;
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';
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
------------------------------ ------------------------------
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;
SQL> flashback table scott.emp to restore point test_rp;
Done
9.查看效果
SQL> select * from emp;
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
----- ---------- --------- ----- ----------- --------- --------- ------
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
------ -------------- -------------
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) - ？？？？？？？？
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.
[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
[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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-764055/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
更新大表中某个字段的存储过程
下一篇：
闪回删除
请登录后发表评论
登录
全部评论
