09:23:45 SCOTT@db252>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.04
09:23:57 SCOTT@db252>insert into dept values(50,'WHAT\'S THAT','BEIJING');
ERROR:
ORA-01756: quoted string not properly terminated
Elapsed: 00:00:00.06
09:24:38 SCOTT@db252>insert into dept values(50,q'[WHAT'S THAT]','BEIJING');
1 row created.
Elapsed: 00:00:00.04
09:25:13 SCOTT@db252>commit;
Commit complete.
Elapsed: 00:00:00.00
09:25:16 SCOTT@db252>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 WHAT'S THAT BEIJING
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.02
09:29:10 SCOTT@db252>select * from dept versions between timestamp to_timestamp('2014-06-06 09:10:48','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2014-
06-06 09:26:48','yyyy-mm-dd hh24:mi:ss');
DEPTNO DNAME LOC
---------- -------------- -------------
50 WHAT'S THAT BEIJING
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.05
09:32:26 SCOTT@db252>select * from dept versions between timestamp to_timestamp('2014-06-06 09:10:48','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2014-
06-06 09:12:48','yyyy-mm-dd hh24:mi:ss');
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.02
09:32:39 SCOTT@db252>
flashback六大技术之flashback version query
环境：
- 23:47:03 hr@ORCL (^ω^) select * from v$version where rownum=1;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
1 原理介绍
flashback query只能“穿越”到过去的某个时间点上的数据库版本，但是在当前时间和过去的某个时间点上，一个表中的数据可能已经被变更多次，单一版本可能无法满足恢复的需求。通过flashback version query能够查看指定时间段内undo表空间中被提交的记录的不同版本。
flashback version query的伪列说明
|
versions_startscn versions_starttime |
记录了操作时的SCN或时间，如果为空，表示在查询范围外创建的 |
|
versions_endscn versions_endtime |
记录了失效时的SCN或时间，配合version_operation列查看，如果为空，或者被删除、或者 该记录当前时间在当前表不存在 |
| versions_operation | I：insert D：delete U：update |
| versions_operation | 事务ID |
2 实验
- 23:38:26 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 3930808
- 23:38:48 hr@ORCL (^ω^) update t1 set empno=122 where ename='Water';
- 已更新 1 行。
- 23:39:42 hr@ORCL (^ω^) commit;
- 提交完成。
- 23:40:06 hr@ORCL (^ω^) delete t1 where rownum<5;
- 已删除4行。
- 23:40:25 hr@ORCL (^ω^) commit;
- 提交完成。
- 23:40:29 hr@ORCL (^ω^) insert into t1 values(155,'qao');
- 已创建 1 行。
- 23:40:56 hr@ORCL (^ω^) insert into t1 values(156,'qinqin');
- 已创建 1 行。
- 23:41:19 hr@ORCL (^ω^) commit;
- 提交完成。
- 23:41:22 hr@ORCL (^ω^) select dbms_flashback.get_system_change_number from dual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 3931066
- 23:46:34 hr@ORCL (^ω^) select empno,ename,versions_startscn,versions_endscn,versions_operation,versions_xid
- 23:47:00 2 from t1 versions between scn 3930808 and 3931066;
- EMPNO ENAME VERSIONS_STARTSCN VERSIONS_ENDSCN VE VERSIONS_XID
- ---------- ---------- ----------------- --------------- -- ----------------
- 155 qao 3930985 I 04002000D4030000
- 111 Linshuibin 3930897 D 06002F00B6040000
- 122 Water 3930897 D 06002F00B6040000
- 122 Water 3930883 3930897 U 02001100BD040000
- 111 Water 3930883
- 111 Linshuibin 3930897
- 7566 JONES 3930897 D 06002F00B6040000
- 7521 WARD 3930897 D 06002F00B6040000
- 7521 WARD 3930897
- 7566 JONES 3930897
- 7788 SCOTT
- 7844 TURNER
- 7900 JAMES
- 7902 FORD
- 156 qinqin 3930985 I 04002000D4030000
- 110 Think
- 已选择16行。
关于flashback versions query 和flashback transaction query 特性
前面提到了oracle 自9i以来的flashback query 特性，借助该特性，可以看到某个过去某个时间点的某张表的数据，这为我们修正dml 误操作等提供了很大的方便。
但是对于该特性来说，如果只是想回退某个时间点以来的部分数据，那么纯粹依靠前后的数据对比来判断，就会显的非常吃力，或许根本就是无法办到的。比如，你某时刻同时跑起了多个job作业，
如果一个job有问题，而其他的job正常完成的，此时，如果你仅仅是想回复错误job处理过的数据，而要保留正常job的处理结果，但是或许你无法判断后面变化的数据哪些是正常job的，哪些是错误job的，因此，很难进行部分数据的修复工作。
10g提供了flashback versions query 和 flashback transaction query 特性，它强化了9i 的flashback query 特性，正是解决上面类似问题的利器。
1.flashback versions query
flashback versions query 能够得到某个时间段内，某些数据行的所有不同版本。这里的版本以事务为单位，事务中的每次数据变化就是一个版本。
举例说明，下面的会话2次update id=1 时的 name 值，因此分别提交过2次，因此是2个不同的事务，在flashback versions query 的结果中，关于
id=1 ，我们看到了3个不同的版本，一个是原来的值，一个是第一次修改的值，一个是第二次修改的值；通过flashback versions query 中的versions_startscn,versions_starttime
versions_endscn, versions_endtime,versions_xid, versions_operation 等伪列，我们也获得了相关版本的开始scn，结束scn，开始时间和结束时间，以及做dml操作的具体transaction id (xid)，以及操作类型等；
同时，我们也看到对于id=1的数据行，三个版本的VERSIONS_ENDSCN,VERSIONS_ENDtime 和 VERSIONS_startscn，VERSIONS_starttime 是承上启下的，一个版本的结束，也就是下一个版本的开始。
15:37:22 SQL> select * from test;
ID NAME
---------- --------------------
1 kkk
2 fff
4 kkk
Executed in 0.031 seconds
15:37:34 SQL> update test set name='TEST' where id=1;
1 row updated
Executed in 0.015 seconds
15:37:54 SQL> commit;
Commit complete
Executed in 0 seconds
15:37:56 SQL> update test set name='TEST_new' where id=1;
1 row updated
Executed in 0.015 seconds
15:38:04 SQL> commit;
Commit complete
Executed in 0 seconds
15:38:06 SQL>
15:39:12 SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 id,name
5 FROM test
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2008-04-27 15:37:30', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2008-04-27 15:38:30', 'YYYY-MM-DD HH24:MI:SS')
9 ;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID NAME
----------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ---------------- ------------------ ---------- --------------------
819393 27-4月 -08 03.38.06 下午 040015006F010000 U 1 TEST_new
819387 27-4月 -08 03.37.54 下午 819393 27-4月 -08 03.38.06 下午 0500150088010000 U 1 TEST
819387 27-4月 -08 03.37.54 下午 1 kkk
2 fff
4 kkk
Executed in 0.031 seconds
15:39:13 SQL> select * from test;
ID NAME
---------- --------------------
1 TEST_new
2 fff
4 kkk
Executed in 0 seconds
15:43:52 SQL>
2. flashback transaction query
flashback transaction query 其实就是查询历史的事务信息，通过查询，我们可以得到过去某个事务操作信息，包括改变的数据行rowid，事务开始和结束时间，事务对应的loggon user，以及用来撤销某个数据行改变的undo sql等。
flashback transaction query 其实就是查询 FLASHBACK_TRANSACTION_QUERY 视图。
比如，通过上面 versions query 得到的xid，我们可以得到这些xid 对应的信息。
15:43:52 SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('&1');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- -------------------------------- ---------- ---------- ------------------------------ --------------------------------------------------------------------------------
040015006F010000 UPDATE 819387 819393 TEST1 update "TEST1"."TEST" set "NAME" = 'TEST' where ROWID = 'AAAM1/AAEAAAAGsAAA';
040015006F010000 BEGIN 819387 819393 TEST1
Executed in 0.297 seconds
15:57:34 SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('&1');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- -------------------------------- ---------- ---------- ------------------------------ --------------------------------------------------------------------------------
0500150088010000 UPDATE 819184 819387 TEST1 update "TEST1"."TEST" set "NAME" = 'kkk' where ROWID = 'AAAM1/AAEAAAAGsAAA';
0500150088010000 BEGIN 819184 819387 TEST1
Executed in 0.032 seconds
15:58:07 SQL>
3.一个例子
flashback versions query 和flashback transaction query 一般都是结合起来用的，通过前者得到某个时间段的某些数据的多个版本的xid，然后通过后者得到具体的xid的操作信息及undo_sql，最后根据需要做适当的数据修复操作。
SQL> create table test (id number ,name varchar2(20));
Table created
SQL> set time on
14:55:14 SQL> set timing on
14:55:18 SQL> insert into test values ( 1,'kkk');
1 row inserted
Executed in 0.016 seconds
14:55:27 SQL> insert into test values ( 2,'fff');
1 row inserted
Executed in 0.016 seconds
14:55:34 SQL> commit;
Commit complete
Executed in 0 seconds
14:55:39 SQL>
此时打开第二个会话窗口，执行如下：
SQL> insert into test1.test values ( 4,'kkk');
1 row inserted
SQL> commit;
Commit complete
SQL>
--再回到第一个窗口，进行 flashback versions query
15:00:20 SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 id,name
5 FROM test
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2008-04-27 14:55:20', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2008-04-27 14:57:00', 'YYYY-MM-DD HH24:MI:SS')
9 ;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID NAME
----------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ---------------- ------------------ ---------- --------------------
817371 27-4月 -08 02.55.39 下午 0900070081010000 I 2 fff
817371 27-4月 -08 02.55.39 下午 0900070081010000 I 1 kkk
817386 27-4月 -08 02.56.03 下午 0700280072010000 I 4 kkk
Executed in 0.016 seconds
15:00:21 SQL>
-- flashback transaction query
15:00:21 SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('&1');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- -------------------------------- ---------- ---------- ------------------------------ --------------------------------------------------------------------------------
0900070081010000 INSERT 817359 817371 TEST1 delete from "TEST1"."TEST" where ROWID = 'AAAM1/AAEAAAAGsAAB';
0900070081010000 INSERT 817359 817371 TEST1 delete from "TEST1"."TEST" where ROWID = 'AAAM1/AAEAAAAGsAAA';
0900070081010000 BEGIN 817359 817371 TEST1
Executed in 0.593 seconds
15:03:54 SQL>
15:11:27 SQL>
15:11:27 SQL>
15:11:27 SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('&1');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- -------------------------------- ---------- ---------- ------------------------------ --------------------------------------------------------------------------------
0700280072010000 INSERT 0 817386 TEST2 delete from "TEST1"."TEST" where ROWID = 'AAAM1/AAEAAAAGwAAA';
0700280072010000 BEGIN 0 817386 TEST2
Executed in 0.031 seconds
15:11:47 SQL>
4.undo_rentention
和9i flashback query 一样，flashback versions query和flashback transaction query 也都是依赖于undo tablespace，依赖于undo_retention 的。换句话说，对于已经不存在于undo tablespace 的数据版本信息，
你当然是无法通过 这些特性把它查出来的。因此设置好undo tablespace 的大小，设置好undo_rentention 的大小就尤为重要(10g 虽然有自动tuning undo_retention 的功能，不过考量并设置合适的undo_retention 最小值也是很有必要的）
