闪回一张更新过的表
今天就做一个闪回的实验吧
sqlplus / as sysdba
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
sqlplus scott/tiger
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
每个人都加1000奖金
SQL> update e set comm=1000;
14 rows updated.
SQL> commit;
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 1000 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 1000 30
7566 JONES MANAGER 7839 02-APR-81 2975 1000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1000 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 1000 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 1000 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 1000 20
7839 KING PRESIDENT 17-NOV-81 5000 1000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1000 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 1000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 1000 30
7902 FORD ANALYST 7566 03-DEC-81 3000 1000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1000 10
14 rows selected.
接着呢后面的事物还在继续不能断啊？所以我们在此要先把这张表做个备份，以防万一。(备份表也会吧，insert部分在此就不演示了。相信你已经知道后面的该怎么做了)
假设做了这步操作
insert into e select * from emp where deptno=10;
commit;
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 1000 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 1000 30
7566 JONES MANAGER 7839 02-APR-81 2975 1000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1000 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 1000 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 1000 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 1000 20
7839 KING PRESIDENT 17-NOV-81 5000 1000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1000 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 1000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 1000 30
7902 FORD ANALYST 7566 03-DEC-81 3000 1000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
17 rows selected.
从现在开始呢我们就要开始恢复了。
sqlplus scott/tiger
SQL> select versions_xid,empno,ename,sal,comm from e versions between scn minvalue and maxvalue ;
VERSIONS_XID EMPNO ENAME SAL COMM
---------------- ---------- ---------- ---------- ----------
0A0021005D000000 7782 CLARK 2450
0A0021005D000000 7839 KING 5000
0A0021005D000000 7934 MILLER 1300
090024005D000000 7934 MILLER 1300 1000
090024005D000000 7902 FORD 3000 1000
090024005D000000 7900 JAMES 950 1000
090024005D000000 7876 ADAMS 1100 1000
090024005D000000 7844 TURNER 1500 1000
090024005D000000 7839 KING 5000 1000
090024005D000000 7788 SCOTT 3000 1000
090024005D000000 7782 CLARK 2450 1000
VERSIONS_XID EMPNO ENAME SAL COMM
---------------- ---------- ---------- ---------- ----------
090024005D000000 7698 BLAKE 2850 1000
090024005D000000 7654 MARTIN 1250 1000
090024005D000000 7566 JONES 2975 1000
090024005D000000 7521 WARD 1250 1000
090024005D000000 7499 ALLEN 1600 1000
090024005D000000 7369 SMITH 800 1000
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
VERSIONS_XID EMPNO ENAME SAL COMM
---------------- ---------- ---------- ---------- ----------
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
31 rows selected.
大家看到没有，versions_xid可以查询刚做了DML操作的相关信息，那么下面我就来看看这个家伙到底做了哪些事情。
SQL> col OPERATION for a10
SQL> col UNDO_SQL for a80
SQL> select OPERATION,UNDO_SQL from flashback_transaction_query where xid=hextoraw('090024005D000000');
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAN';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAM';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAL';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAK';
UPDATE
update "SCOTT"."E" set "COMM" = '0' where ROWID = 'AAACYcAABAAAG4CAAJ';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAI';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAH';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAG';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAF';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."E" set "COMM" = '1400' where ROWID = 'AAACYcAABAAAG4CAAE';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAD';
UPDATE
update "SCOTT"."E" set "COMM" = '500' where ROWID = 'AAACYcAABAAAG4CAAC';
OPERATION
----------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "SCOTT"."E" set "COMM" = '300' where ROWID = 'AAACYcAABAAAG4CAAB';
UPDATE
update "SCOTT"."E" set "COMM" = NULL where ROWID = 'AAACYcAABAAAG4CAAA';
BEGIN
哇塞这个家伙都干了这些事情，那么我们该怎么办呢？当然是恢复啦。请看下面操作，我们要根据他的SCN号来恢复，千万别眨眼间，请看操作。
SQL> select OPERATION,START_SCN from flashback_transaction_query where xid=hextoraw('090024005D000000');
OPERATION START_SCN
---------- ----------
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
UPDATE 247381
OPERATION START_SCN
---------- ----------
UPDATE 247381
UPDATE 247381
UPDATE 247381
BEGIN 247381
15 rows selected.
看到了吧。那么我们就恢复到这个地方。
SQL> flashback table scott.e to scn 247381;
flashback table scott.e to scn 247381
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
打开行的移动功能，由于默认是关闭的，所以会报错
SQL> alter table scott.e enable row movement;
Table altered.
SQL> flashback table scott.e to scn 247381;
Flashback complete.
SQL>
ok闪回完成了
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
good 正是这14条语句
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24531354/viewspace-694690/ ，如需转载，请注明出处，否则将追究法律责任。
