平凡的幸福(備站）

昨天用Flashback救回一個不小心覆蓋到舊資料的Procedure，今天再度測試。

建立一個測試用的Procedure
SQL> CREATE OR REPLACE PROCEDURE APPS.TEST_PROC
2 IS
3 BEGIN
4 NULL;
5 END;
6 /

Procedure created.

查詢dba_source是否有資料
SQL> select count(1) from dba_source where name='TEST_PROC';

COUNT(1)
----------
5

刪除測試的Procedure
SQL> drop procedure apps.test_proc;

Procedure dropped.

再查一次dba_source，也可以查dba_objects
SQL> select count(1) from dba_source where name='TEST_PROC';

COUNT(1)
----------
0

登入到sys
SQL> connect sys/xxxxx as sysdba
Connected.

利用flashback查30分鐘前的資料（假設undo的資料還在）
SQL> select text from dba_source AS OF TIMESTAMP TO_TIMESTAMP(sysdate-30/1440)
where owner='APPS' and name = 'TEST_PROC';

TEXT
--------------------------------------------------------------------------------
PROCEDURE TEST_PROC
IS
BEGIN
dbms_output.put_line('aaaaaa');
END;