天萃荷净
使用flashback query恢复被删除plsql,从10g及其以后的版本中,如果被删除的plsql被及时发现(undo 未被覆盖掉)可以使用flashback query功能实现恢复.
今天一个朋友在11g中误删除生产中的一个过程,让他通过对dba_source视图的flashback query找回该过程.
1.创建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.oracleplus.com');
www.oracleplus.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.
2.恢复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;
3.重建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('oracleplus');
oracleplus
PL/SQL procedure successfully completed.
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oralce闪回查询 使用flashback query恢复被删除plsql