♣
题目部分
在Oracle中，什么是闪回事务查询（Flashback Transaction Query）？
♣
答案部分
闪回事务查询（Flashback Transaction Query）是查看某个事务或所有事务在过去一段时间对数据进行的修改。
闪回事务查询提供了一种查看事务级数据库变化的方法。它是SQL的扩展，能够看到事务带来的所有变化。此外，返回补充SQL语句，并用于撤消由事务引起的各行变化。使用闪回事务查询的权限：
代码语言：javascript复制GRANT SELECT ANY TRANSACTION TO LHR;
GRANT EXECUTE ON DBMS_FLASHBACK TO LHR;
开启补全日志：
代码语言：javascript复制ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY,UNIQUE INDEX) COLUMNS;
闪回事务查询实际上是查询的数据字典FLASHBACK_TRANSACTION_QUERY。可以根据该视图的UNDO_SQL列值返回数据以前版本。
闪回事务查询示例说明：
代码语言：javascript复制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
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
