sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oralce闪回查询 使用flashback query恢复被删除plsql

时间:2016-07-22 22:57   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 使用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

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/839.html

Oracle研究中心

关键词:

Oralce闪回查询

使用flashback query恢复被删除plsql

flashback query使用案例