使用flashback query恢复被删除plsql

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:使用flashback query恢复被删除plsql

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天一个朋友在11g中误删除生产中的一个过程,让他通过对dba_source视图的flashback query找回该过程.
从10g及其以后的版本中,如果被删除的plsql被及时发现(undo 未被覆盖掉)可以使用flashback query功能实现恢复.
创建plsql并删除

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create or replace PROCEDURE  p_test_del(in_put varchar2) as
  2  begin
  3  dbms_output.put_line(in_put);
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on
SQL> exec p_test_del('www.xifenfei.com');
www.xifenfei.com

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012-07-02 08:34:45

SQL> drop  PROCEDURE  p_test_del;

Procedure dropped.

恢复plsql

SQL> set pagesize 0
SQL> column text format a4000
SQL> spool /tmp/get_delete_proc.xff
SQL> SELECT text
  2         FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP('2012-07-02 08:34:45', 'YYYY-MM-DD HH24:MI:SS')
  3        WHERE OWNER = 'CHF' AND NAME = 'P_TEST_DEL' ORDER BY LINE;
PROCEDURE  p_test_del(in_put varchar2) as
begin
dbms_output.put_line(in_put);
end;

SQL> spool off;
SQL> !more /tmp/get_delete_proc.xff
SQL> SELECT text
  2         FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP('2012-07-02 08:34:45', 'YYYY-MM-DD HH24:MI:SS')
  3        WHERE OWNER = 'CHF' AND NAME = 'P_TEST_DEL' ORDER BY LINE;
create PROCEDURE  p_test_del(in_put varchar2) as 
begin                         
dbms_output.put_line(in_put); 
end;        

SQL> spool off;

重建plsql

SQL> create PROCEDURE  p_test_del(in_put varchar2) as 
  2  begin                                     
  3  dbms_output.put_line(in_put);             
  4  end; 
  5  /

Procedure created.

SQL> set serveroutput on
SQL> exec p_test_del('惜分飞');
惜分飞

PL/SQL procedure successfully completed.
此条目发表在 Oracle备份恢复 分类目录。将固定链接加入收藏夹。

使用flashback query恢复被删除plsql》有 3 条评论

  1. 惜分飞 说:

    找回视图测试

    SQL> create view v_xifenfei as
      2  select * from user_objects;
    
    View created.
    
    SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    
    TO_CHAR(SYSDATE,'YY
    -------------------
    2012-01-12 18:12:33
    
    SQL> drop view v_xifenfei;
    
    View dropped.
    
    SQL> CONN / AS SYSDBA
    Connected.
    
    SQL> SET LONG 1000
    SQL> SELECT text
      2     FROM dba_views AS OF TIMESTAMP TO_TIMESTAMP('2012-01-12 18:12:33', 'YYYY-MM-DD HH24:MI:SS')
      3    WHERE OWNER = 'CHF' AND VIEW_NAME = 'V_XIFENFEI';
    
    TEXT
    --------------------------------------------------------------------------------
    select "OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE"
    ,"CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDA
    RY","NAMESPACE","EDITION_NAME" from user_objects
    
  2. 汪浩 说:

    权限不足应该如何处理?

  3. 惜分飞 说:
    set pagesize 0
    column text format a4000
    spool /tmp/get_delete_proc.xff
    SELECT text
           FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP('2012-07-02 08:34:45', 'YYYY-MM-DD HH24:MI:SS')
          WHERE OWNER = UPPER('&USERNAME') AND NAME = UPPER('&PROC_NAME') ORDER BY LINE;
    spool off;