在oracle10g版本之前,如果用户对一个表进行了错误的DML操作并且提交了这些操作,而且之前用户也没备份,要恢复该表到操作之前的状态,就要对这个表进行不完全回复。oracle10g中只要使用一条命令就可以了!oracle是利用还原段中的数据来执行这一恢复的。
提交DML操作之后,该操作所使用的还原段就可以被其他的操作使用了,为了保证在进行闪回操作时这些数据仍然在还原段中,可能要重新设置undoretention的数值,单位为妙,表示事务提交的数据至少在还原段里停留的时间。
实验如下:
SQL> create table test as select * from emp;--首先创建一个test表用于实验。
表已创建。
SQL> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择14行。
SQL> conn system/yang --
已连接。
2 更改undo_retention的值。
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention =7200 ;
系统已更改。
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 7200
3 对test表进行DML操作
SQL> update test set sal =9999;
已更新14行。
4。接下来,使用带有version子句的查询语句获取刚刚所做的DML操作的version_ID 和其他相关信息。versions_xid,-- 事务号,minvalue 最小值, maxvalue 最大值
SQL> select versions_xid,empno,ename,sal
2 from test
3 versions between scn minvalue and maxvalue
4 where empno =7900;
VERSIONS_XID EMPNO ENAME SAL
---------------- ---------- ---------- ----------
7900 JAMES 950
由于还没有提交DML操作,所以versionid 为空。进行提交。
SQL> commit;
提交完成。
SQL> select versions_xid,empno,ename,sal
2 from test
3 versions between scn minvalue and maxvalue
4 where empno =7900;
VERSIONS_XID EMPNO ENAME SAL
---------------- ---------- ---------- ----------
0800050012030000 7900 JAMES 9999
7900 JAMES 950
此时 versionid 已经 有值了,为了使用闪回功能,我们要进入到system用户下。
SQL> conn system/yang
已连接。
SQL> col operation for a10
SQL> col undo_sql for a80
SQL> select operation,undo_sql
2 from flashback_transaction_query
3 where xid = hextoraw('0800050012030000');
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."TEST" set "SAL" = '1300' where ROWID = 'AAARWCAAEAAAAGMAAN';
UPDATE
update "SCOTT"."TEST" set "SAL" = '3000' where ROWID = 'AAARWCAAEAAAAGMAAM';
UPDATE
update "SCOTT"."TEST" set "SAL" = '950' where ROWID = 'AAARWCAAEAAAAGMAAL';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."TEST" set "SAL" = '1100' where ROWID = 'AAARWCAAEAAAAGMAAK';
UPDATE
update "SCOTT"."TEST" set "SAL" = '1500' where ROWID = 'AAARWCAAEAAAAGMAAJ';
UPDATE
update "SCOTT"."TEST" set "SAL" = '5000' where ROWID = 'AAARWCAAEAAAAGMAAI';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."TEST" set "SAL" = '3000' where ROWID = 'AAARWCAAEAAAAGMAAH';
UPDATE
update "SCOTT"."TEST" set "SAL" = '1250' where ROWID = 'AAARWCAAEAAAAGMAAC';
UPDATE
update "SCOTT"."TEST" set "SAL" = '2975' where ROWID = 'AAARWCAAEAAAAGMAAD';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."TEST" set "SAL" = '1250' where ROWID = 'AAARWCAAEAAAAGMAAE';
UPDATE
update "SCOTT"."TEST" set "SAL" = '2850' where ROWID = 'AAARWCAAEAAAAGMAAF';
UPDATE
update "SCOTT"."TEST" set "SAL" = '2450' where ROWID = 'AAARWCAAEAAAAGMAAG';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."TEST" set "SAL" = '1600' where ROWID = 'AAARWCAAEAAAAGMAAB';
UPDATE
update "SCOTT"."TEST" set "SAL" = '800' where ROWID = 'AAARWCAAEAAAAGMAAA';
BEGIN
已选择15行。
查询test表的内容,看见所有的工资都变为了9999
SQL> select * from scott.test ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 9999
20
7499 ALLEN SALESMAN 7698 20-2月 -81 9999 300
30
7521 WARD SALESMAN 7698 22-2月 -81 9999 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 9999
20
7654 MARTIN SALESMAN 7698 28-9月 -81 9999 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 9999
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 9999
10
7788 SCOTT ANALYST 7566 19-4月 -87 9999
20
7839 KING PRESIDENT 17-11月-81 9999
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 9999 0
30
7876 ADAMS CLERK 7788 23-5月 -87 9999
20
7900 JAMES CLERK 7698 03-12月-81 9999
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 9999
20
7934 MILLER CLERK 7782 23-1月 -82 9999
10
已选择14行。
6 如果要对刚才的操作进行恢复,则先要使用下面的查询获得DML事务所对应的SCN号
SQL> select operation,START_SCN
2 from flashback_transaction_query
3 where xid=hextoraw('0800050012030000');
OPERATION START_SCN
---------- ----------
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
OPERATION START_SCN
---------- ----------
UPDATE 1371887
UPDATE 1371887
UPDATE 1371887
BEGIN 1371887
已选择15行。
---------------这个是当前的SCN号-------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1372969
SQL> flashback table scott.test to SCN 1371887;
flashback table scott.test to SCN 1371887
*
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表
出现此错误是因为在闪回一个表之前,要先将该表的行移动功能打开,oracle默认是关闭的。
SQL> alter table scott.test enable row movement;
表已更改。
执行闪回操作。
SQL> flashback table scott.test to SCN 1371887;
闪回完成。
SQL> select * from scott.test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择14行。
成功。。。。。。