闪回查询(SELECT AS OF)
使用Flashback Query的场景包括如下:
摘自官档
Recovering lost data or undoing incorrect, committed changes.
For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
Comparing current data with the corresponding data at an earlier time.
For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
Checking the state of transactional data at a particular time.
For example, you can verify the account balance of a certain day.
Simplifying application design by removing the need to store some kinds of temporal data.
Oracle Flashback Query lets you retrieve past data directly from the database.
Applying packaged applications, such as report generation tools, to past data.
Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.
- 需要授予 FLASHBACK ANY TABLE 权限
假设表
personnel
在早上4点30的时候发现Smith
的记录被删,DBA确定在前天晚上7点30的时候数据是准确的,于是我们可以使用Flashback Query
技术找回丢失的数据查纪录
SELECT * FROM personnel AS OF TIMESTAMP TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE UPPER(last_name) = 'SMITH';使用闪回查询恢复数据
INSERT INTO personnel ( SELECT * FROM personnel AS OF TIMESTAMP TO_TIMESTAMP('2012-03-21 07:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE UPPER(last_name) = 'SMITH' );
创建过去表的一个视图
CREATE VIEW hour_ago AS SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
- 可以在自连接中使用
AS OF
子句,或者INTERSECT
和MINUS
提取或者比较两个不同时间点的数据
也可以使用下面的方法恢复丢失的数据
INSERT INTO employees (SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE) MINUS SELECT * FROM employees;
场景
CREATE TABLE flashback_query_test ( id NUMBER(10) ); SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM ----------- ------------------- 722452 2004-03-29 13:34:12 INSERT INTO flashback_query_test (id) VALUES (1); COMMIT; SELECT COUNT(*) FROM flashback_query_test; COUNT(*) ---------- 1 SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS'); COUNT(*) ---------- 0 SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452; COUNT(*) ---------- 0
使用DBMS_FLASHBACK
进程闪回查询
This can as well been done with SCN using ENABLE_AT_SYSTEM_CHANGE_NUMBER
procedure or timestamp usingENABLE_AT_TIME
procedure:
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS current_time FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME
----------- --------------------------------------------------------------------------- -----------------------------
28954179 15-AUG-11 11.03.48.000000000 AM 15-aug-2011 11:03:48
SQL> EXEC dbms_flashback.enable_at_system_change_number(28954179);
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM test1;
ID DESCR
---------- ------------------------------
1 One
2 Two
3 Three
SQL> EXEC dbms_flashback.disable;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM test1;
ID DESCR
---------- ------------------------------
1 TEMPORARY
2 TEMPORARY
3 TEMPORARY
最后,闪回表到过去,(效果和上面的insert into as select一样)
SQL> flashback TABLE test1 TO scn 28954179;
Flashback complete.
SQL> SELECT * FROM test1;
ID DESCR
---------- ------------------------------
1 One
2 Two
3 Three