闪回查询(SELECT AS OF)

时间:2021-04-22 20:42:13

使用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子句,或者INTERSECTMINUS提取或者比较两个不同时间点的数据

也可以使用下面的方法恢复丢失的数据

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:

  1. SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS current_time FROM v$database;
  2. CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME
  3. ----------- --------------------------------------------------------------------------- -----------------------------
  4. 28954179 15-AUG-11 11.03.48.000000000 AM 15-aug-2011 11:03:48
  5. SQL> EXEC dbms_flashback.enable_at_system_change_number(28954179);
  6. PL/SQL PROCEDURE successfully completed.
  7. SQL> SELECT * FROM test1;
  8. ID DESCR
  9. ---------- ------------------------------
  10. 1 One
  11. 2 Two
  12. 3 Three
  13. SQL> EXEC dbms_flashback.disable;
  14. PL/SQL PROCEDURE successfully completed.
  15. SQL> SELECT * FROM test1;
  16. ID DESCR
  17. ---------- ------------------------------
  18. 1 TEMPORARY
  19. 2 TEMPORARY
  20. 3 TEMPORARY

最后,闪回表到过去,(效果和上面的insert into as select一样)

  1. SQL> flashback TABLE test1 TO scn 28954179;
  2. Flashback complete.
  3. SQL> SELECT * FROM test1;
  4. ID DESCR
  5. ---------- ------------------------------
  6. 1 One
  7. 2 Two
  8. 3 Three