閃回查詢恢復過程

yangtingkun發表於2009-07-07

今天在ITPUB上有人問如何恢復誤刪除的一個過程,如果通過備份來進行恢復不但代價大,而且恢復速度慢,其實用閃回查詢就可以恢復。

 

 

看一個簡單的例子:

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.29.2版本上,是可以用這個方法來恢復過程、函式、包和觸發器的。

也許有人有這樣的疑問,閃回查詢是不支援DDL的。閃回查詢確實不支援DDL,如果查詢的表執行了DDL,則不能閃回查詢DDL以前的表的資料。而CREATE OR REPLACEDROP PRCOEDURE也確實是DDL語句。但是過程的建立雖然是DDL,實際上是對後臺資料字典表做的DML修改,而這些資料字典表是可以閃回查詢的。

最後提一句,DBAALLUSER開頭的檢視由於基表是CLUSTER表,因此可以執行閃回查詢,而V$GV$開頭的動態效能檢視所依賴的並不是真正的資料庫中的表,而是Oracle通過C程式實現的偽表,因此這些檢視無法利用閃回查詢的功能。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-608556/,如需轉載,請註明出處,否則將追究法律責任。

相關文章