【DB笔试面试811】在Oracle中，什么是闪回事务查询（Flashback Transaction Query）？

AiDBA宝典

发布于 2020-06-04 10:39:12

5950

发布于 2020-06-04 10:39:12

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在Oracle中，什么是闪回事务查询（Flashback Transaction Query）？

♣

答案部分

闪回事务查询（Flashback Transaction Query）是查看某个事务或所有事务在过去一段时间对数据进行的修改。

闪回事务查询提供了一种查看事务级数据库变化的方法。它是SQL的扩展，能够看到事务带来的所有变化。此外，返回补充SQL语句，并用于撤消由事务引起的各行变化。使用闪回事务查询的权限：

GRANT SELECT  ANY  TRANSACTION TO  LHR;
GRANT EXECUTE ON DBMS_FLASHBACK TO LHR;

开启补全日志：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY,UNIQUE INDEX)  COLUMNS;

闪回事务查询实际上是查询的数据字典FLASHBACK_TRANSACTION_QUERY。可以根据该视图的UNDO_SQL列值返回数据以前版本。

闪回事务查询示例说明：

SQL> conn / as sysdba
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> grant select any transaction to lhr;
Grant succeeded.
SQL> conn lhr/lhr

LHR@orclasm > SELECT TO_DATE(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
------------------- -------------------------
2017-06-17 16:27:29                  67854536

LHR@orclasm > CREATE TABLE T_FTQ_20170617_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO=7902;

Table created.

LHR@orclasm > SELECT * FROM T_FTQ_20170617_LHR;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

LHR@orclasm > UPDATE T_FTQ_20170617_LHR SET SAL=9999 WHERE EMPNO=7902;

1 row updated.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
------------------- -------------------------
2017-06-17 16:28:25                  67854637

LHR@orclasm >  UPDATE T_FTQ_20170617_LHR SET SAL=99999 WHERE EMPNO=7902;

1 row updated.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
------------------- -------------------------
2017-06-17 16:28:41                  67854673

LHR@orclasm > col UNDO_SQL format a100
LHR@orclasm > col TABLE_NAME format a30
LHR@orclasm > SELECT START_SCN, COMMIT_SCN, LOGON_USER, OPERATION, TABLE_NAME, UNDO_SQL
  2    FROM FLASHBACK_TRANSACTION_QUERY V
  3   WHERE XID IN ('08000000AFA50000', '020014002C9F0000')
  4   ORDER BY V.START_SCN;
 START_SCN COMMIT_SCN LOGON_USER   OPERATION   TABLE_NAME           UNDO_SQL
---------- ---------- ------------ ----------- -------------------- ----------------------------------------------------------------------------------------------------
  67854610   67854626 LHR          UPDATE      T_FTQ_20170617_LHR   update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '3000' where ROWID = 'AAAnyVAAEAAAACDAAA';
  67854610   67854626 LHR          BEGIN
  67854645   67854656 LHR          UPDATE      T_FTQ_20170617_LHR   update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA';
  67854645   67854656 LHR          BEGIN

LHR@orclasm > update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA';

1 row updated.

LHR@orclasm >  SELECT * FROM T_FTQ_20170617_LHR;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       9999                    20