利用闪回query 恢复删除的数据及存储过程

来源：互联网发布：商业源码编辑：程序博客网时间：2024/06/09 14:06

今天不小心删除了emp表的几行数据，还好有闪回查询这技术，不过当然还是要保证undo能够用的情况下

scott@ORCL> select * from emp;     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7499 ALLEN      SALESMAN        7698 1981-02-20:00:00:00       1600        300         30      7521 WARD       SALESMAN        7698 1981-02-22:00:00:00       1250        500         30      7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400         30      7698 BLAKE      MANAGER         7839 1981-05-01:00:00:00       2850                    30      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10      7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0         30      7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100                    20      7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950                    30      7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000                    20      7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    1011 rows selected.

利用闪回query 恢复

  1  insert into emp (  2* select * from (select * from emp where empno in (7788,7369,7566))  as of timestamp  sysdate-5/24)scott@ORCL> /3 rows created.scott@ORCL> commit;Commit complete.scott@ORCL> select count(*) from emp;  COUNT(*)----------        14

既然遇到这个问题了就试试如何闪回我们写的存储过程或者函数吧。

  1  create or replace  procedure proc_1  2  as  3  begin  4   dbms_output.put_line('Hello World');  5* end;wsx@ORCL> /

利用闪回查询找回文本内容，接下来只要利用找回的文本内容重新编译即可

sys@ORCL> select text from dba_source as of timestamp sysdate-1/1440 where owner='WSX' and name='PROC_1';TEXT----------------------------------------------------------------------------------------------------procedure proc_1asbegin dbms_output.put_line('Hello World');end;end;6 rows selected.

  1  create or replace procedure proc_1  2  as  3  begin  4   dbms_output.put_line('Hello World');  5* end;wsx@ORCL> /Procedure created.