♣题目 部分
在Oracle中，什么是闪回事务查询（Flashback Transaction Query）？
♣答案部分
闪回事务查询（Flashback Transaction Query）是查看某个事务或所有事务在过去一段时间对数据进行的修改。
闪回事务查询提供了一种查看事务级数据库变化的方法。它是SQL的扩展，能够看到事务带来的所有变化。此外，返回补充SQL语句，并用于撤消由事务引起的各行变化。使用闪回事务查询的权限：
1GRANT SELECT ANY TRANSACTION TO LHR;
2GRANT EXECUTE ON DBMS_FLASHBACK TO LHR;
开启补全日志：
1ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
2ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY,UNIQUE INDEX) COLUMNS;
闪回事务查询实际上是查询的数据字典FLASHBACK_TRANSACTION_QUERY。可以根据该视图的UNDO_SQL列值返回数据以前版本。
闪回事务查询示例说明：
1SQL> conn / as sysdba
2SQL> alter database add supplemental log data;
3Database altered.
4SQL> alter database add supplemental log data (primary key) columns;
5Database altered.
6SQL> grant select any transaction to lhr;
7Grant succeeded.
8SQL> conn lhr/lhr
9
10LHR@orclasm > SELECT TO_DATE(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
11
12TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
13------------------- -------------------------
142017-06-17 16:27:29 67854536
15
16LHR@orclasm > CREATE TABLE T_FTQ_20170617_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO=7902;
17
18Table created.
19
20LHR@orclasm > SELECT * FROM T_FTQ_20170617_LHR;
21
22 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
23---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
24 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
25
26LHR@orclasm > UPDATE T_FTQ_20170617_LHR SET SAL=9999 WHERE EMPNO=7902;
27
281 row updated.
29
30LHR@orclasm > COMMIT;
31
32Commit complete.
33
34LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
35
36TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
37------------------- -------------------------
382017-06-17 16:28:25 67854637
39
40LHR@orclasm > UPDATE T_FTQ_20170617_LHR SET SAL=99999 WHERE EMPNO=7902;
41
421 row updated.
43
44LHR@orclasm > COMMIT;
45
46Commit complete.
47
48LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;
49
50TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)
51------------------- -------------------------
522017-06-17 16:28:41 67854673
53
54LHR@orclasm > col UNDO_SQL format a100
55LHR@orclasm > col TABLE_NAME format a30
56LHR@orclasm > SELECT START_SCN, COMMIT_SCN, LOGON_USER, OPERATION, TABLE_NAME, UNDO_SQL
57 2 FROM FLASHBACK_TRANSACTION_QUERY V
58 3 WHERE XID IN ('08000000AFA50000', '020014002C9F0000')
59 4 ORDER BY V.START_SCN;
60 START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME UNDO_SQL
61---------- ---------- ------------ ----------- -------------------- ----------------------------------------------------------------------------------------------------
62 67854610 67854626 LHR UPDATE T_FTQ_20170617_LHR update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '3000' where ROWID = 'AAAnyVAAEAAAACDAAA';
63 67854610 67854626 LHR BEGIN
64 67854645 67854656 LHR UPDATE T_FTQ_20170617_LHR update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA';
65 67854645 67854656 LHR BEGIN
66
67LHR@orclasm > update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA';
68
691 row updated.
70
71LHR@orclasm > SELECT * FROM T_FTQ_20170617_LHR;
72
73 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
74---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
75 7902 FORD ANALYST 7566 1981-12-03 00:00:00 9999 20
