联系:手机/微信(+86 17813235971) QQ(107644445)
标题:使用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.
找回视图测试
权限不足应该如何处理?