适用范围

Oracle 10G and later

目的

存储过程闪回恢复

详细方案

创建存储过程PROC_TABLE01_DML并将其删除
SQL> create table table01 as select * from dba_objects;
SQL> create or replace procedure proc_table01_dml
  2  as
  3  begin
  4  for i in 1..10 loop
  5  update table01 set object_id=i where object_name='OBJ$';
  6  commit;
  7  end loop;
  8  end;
  9  /

Procedure created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    167411

SQL> select text from dba_source where name='PROC_TABLE01_DML' and type='PROCEDURE';

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure proc_table01_dml
as
begin
for i in 1..10 loop
update table01 set object_id=i where object_name='OBJ$';
commit;
end loop;
end

8 rows selected.
SQL> drop procedure proc_table01_dml;

1、通过dba_source试图闪回查询恢复

SQL> select text from dba_source as of scn 1674113 where name='PROC_TABLE01_DML' and type='PROCEDURE';

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure proc_table01_dml
as
begin
for i in 1..10 loop
update table01 set object_id=i where object_name='OBJ$';
commit;
end loop;
end;

8 rows selected.

2、通过source$和obj$基表闪回查询恢复

SQL> desc source$;