联系：手机(13429648788) 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.orasos.com');
www.orasos.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.
找回视图测试
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权限不足应该如何处理？
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;