oracledml误操作找回数据
DML误操作恢复过程
1在没有提交之前，自己session可以撤销,使用rollback进行回滚
2.提交之后，不能rollback。但是dml事务的提交历史数据在undo中有反操作记录的。默认保存的时间undo_retention=15分钟
SQL> show parameter undo_retention
NAME TYPE VALUE
-------------------------- ---------------------- ----------------------
undo_retention integer 900
SQL>
保留时间之内的历史数据能否被覆盖，由guarantee来确定 通过查看undo表空间确认
SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
----------------------------------------------------- ---------------------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
UNDOTBS2 NOGUARANTEE
GGDATA NOT APPLY
TEMP01 NOT APPLY
R1 NOT APPLY
R2 NOT APPLY
R3 NOT APPLY
R4 NOT APPLY
12 rows selected.
SQL>
如果确保指定时间内保存
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL>
SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------------------------------ ----------------------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 GUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
UNDOTBS2 NOGUARANTEE
GGDATA NOT APPLY
TEMP01 NOT APPLY
R1 NOT APPLY
R2 NOT APPLY
R3 NOT APPLY
R4 NOT APPLY
12 rows selected.
SQL>
如何才能从undo表空间找出我们需要的DML历史数据呢？
使用闪回查询，查询的语句，使用as of scn/timestamp。 查询表在指定scn/timestamp的行记录状态
sql> select …… from t as of scn <> where …… order by ……；
sql> select …… from t as of timestamp <> where …… order by ……；
sql> select …… from t as of timestamp sysdate-1/1440 where …… order by ……； //查询一分钟前 1440=24*60*60-------一天的分钟数
例子：
SQL> create table t as select empno,ename,job,mgr,sal,deptno from emp; ---------------新建表emp1
Table created.
SQL> select * from t;
EMPNO ENAME JOB MGR SAL DEPTNO
---------- -------------------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7788 SCOTT ANALYST 7566 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
14 rows selected.
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
闪回版本查询精确的时间，或者scn
select …… from t versions between scn minvalue and maxvalue where ……；
versions_startscn 开始scn
versions_starttime 开始时间
versions_endscn 结束scn
versions_xid 事务
versions_operation ---执行了什么操作 D delete |I insert |U update
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
模拟dml误操作，删除t表中的数据，然后进行提交commit操作
SQL> delete t;
14 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> select versions_startscn,versions_starttime,versions_operation from t versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_STARTTIME VE
----------------- --------------------------------------------------------------------------- --
11512532 08-JAN-16 05.51.01 PM D // 执行了delete操作
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
11512532 08-JAN-16 05.51.01 PM D
SQL>
SQL> select * from t as of scn 11512532;
no rows selected
SQL> select * from t as of scn 11512531;//scn比着查询的scn-1 ------------------------------通过scn，查询出误操作的数据
EMPNO ENAME JOB MGR SAL DEPTNO
---------- -------------------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7788 SCOTT ANALYST 7566 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
14 rows selected.
SQL> insert into t select * from t as of scn 11512531; 将查询获得的数据重新插入t表
14 rows created.
SQL> commit;
Commit complete.
SQL> select * from t;
EMPNO ENAME JOB MGR SAL DEPTNO
---------- -------------------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7788 SCOTT ANALYST 7566 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
14 rows selected.
SQL> select * from t as of timestamp to_timestamp ('08-JAN-16 17.51.00','DD-Mon-RR HH24:MI:SS.FF'); ----------------通过starttime，查询出误操作的数据 //建议使用scn比较方便
EMPNO ENAME JOB MGR SAL DEPTNO
---------- -------------------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7788 SCOTT ANALYST 7566 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
14 rows selected.
SQL> select * from t as of timestamp sysdate-1/1440; ------------------------如果执行误操作的时间较短，可以简单使用sysdate-n/1440！查询n分钟前的dml误操作
EMPNO ENAME JOB MGR SAL DEPTNO
---------- -------------------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7788 SCOTT ANALYST 7566 3000 20
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
14 rows selected.
SQL>
注意： 正常提交会有。如果ddl/tcl等隐式提交的，这样dml不会在undo表空间记录