03-15 04:12 阅读 171

update误操作后通过undo记录的scn找回原纪录

SQL> select * from t2;

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> update t2 set comm=999 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t2;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 999 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> conn / as sysdba

Connected.

SQL>

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

58802409341

SQL>

SQL> select * from scott.t2 as of scn 58802409335;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 999 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

SQL> select * from scott.t2 as of scn 58802408700;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 999 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> select * from scott.t2 as of scn 58802408000;

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> select * from scott.t2 as of scn 58802408000 where empno=7369;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

SQL> update scott.t2 set COMM=(select COMM from scott.t2 as of scn 58802408000 where empno=7369) where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from scott.t2;

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2756209/，如需转载，请注明出处，否则将追究法律责任。

update误操作后 通过undo记录的scn找回原纪录

update误操作后通过undo记录的scn找回原纪录