看一个简单的例子：

SQL> SELECT TEXT             
  2  FROM DBA_SOURCE
  3  WHERE WNER = 'YANGTK'
  4  AND NAME = 'P_TEST';

TEXT
-----------------------------------------------------------------------------------------
PROCEDURE P_TEST AS
BEGIN
 INSERT INTO T VALUES (1, 'A');
 COMMIT;
END;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2009-07-07 17:28:07

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
              6258680436

SQL> CONN YANGTK/YANGTK
Connected.
SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
  2  BEGIN
  3  NULL;
  4  END;
  5  /

Procedure created.

SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT TEXT        
  2  FROM DBA_SOURCE
  3  WHERE NAME = 'P_TEST'
  4  AND WNER = 'YANGTK'
  5  ORDER BY LINE;

TEXT
----------------------------------------------------------------------------------------
PROCEDURE P_TEST AS
BEGIN
NULL;
END;

SQL> SELECT TEXT
  2  FROM DBA_SOURCE AS OF SCN 6258680436
  3  WHERE NAME = 'P_TEST'
  4  AND WNER = 'YANGTK'
  5  ORDER BY LINE;

TEXT
----------------------------------------------------------------------------------------
PROCEDURE P_TEST AS
BEGIN
 INSERT INTO T VALUES (1, 'A');
 COMMIT;
END;

SQL> SELECT TEXT  
  2  FROM DBA_SOURCE AS OF TIMESTAMP (TO_TIMESTAMP ('2009-07-07 17:28:07', 'YYYY-MM-DD HH24:MI:SS'))
  3  WHERE NAME = 'P_TEST'
  4  AND WNER = 'YANGTK'
  5  ORDER BY LINE;

TEXT
---------------------------------------------------------------------------------------
PROCEDURE P_TEST AS
BEGIN
 INSERT INTO T VALUES (1, 'A');
 COMMIT;
END;

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

在10.2和9.2版本上，是可以用这个方法来恢复过程、函数、包和触发器的。

也许有人有这样的疑问，闪回查询是不支持DDL的。闪回查询确实不支持DDL，如果查询的表执行了DDL，则不能闪回查询DDL以前的表的数据。而CREATE OR REPLACE或DROP PRCOEDURE也确实是DDL语句。但是过程的创建虽然是DDL，实际上是对后台数据字典表做的DML修改，而这些数据字典表是可以闪回查询的。

最后提一句，DBA、ALL、USER开头的视图由于基表是CLUSTER表，因此可以执行闪回查询，而V$、GV$开头的动态性能视图所依赖的并不是真正的数据库中的表，而是Oracle通过C程序实现的伪表，因此这些视图无法利用闪回查询的功能。