使用flashback query恢复被删除plsql

最新推荐文章于 2021-04-03 05:17:22 发布

Amos318

最新推荐文章于 2021-04-03 05:17:22 发布

阅读量893

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

今天一个朋友在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.