on the NEWEMP table. Note that the first two updated are not listed by the Flashback Versions Query.
What could be the reason?
A.The first two updated were not explicitly committed.
B.ALTER TABLE caused the recycle bin to release the space.
C.The data definition language (DDL) operation caused a log switch.
D.Flashback Versions Query stops producing versions of rows that existed before a change in the table
structure.
Answer:C
解析:
A错,隐式提交也能正常查询;
SQL> delete from lbx;
1 row deleted.
SQL> quit
[oracle@demo ~]$ sqlplus lbx/lbx
SQL> select versions_xid as xid,versions_startscn as start_scn,
2 versions_endscn as end_scn,versions_operation as operation,id from lbx
3 versions between scn minvalue and maxvalue where name='a';
XID START_SCN END_SCN O ID
---------------- ---------- ---------- - ----------
03000E00C7030000 1251661 D 1
0400120024030000 1251622 1251661 I 1
--经测试使用DDL语句时,增加列并不导致查询不到历史版本,只有删除列时才会导致历史版本查询失败,为什么?
SQL> desc lbx;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
COL1 NUMBER(38)
COL2 NUMBER(38)
COL3 NUMBER(38)
SQL> select * from lbx;
no rows selected
SQL> select versions_xid as xid,versions_startscn as start_scn,versions_endscn as end_scn,versions_operation as operation,id from lbx versions between scn minvalue and maxvalue where name='a';
no rows selected
--隐式提交
SQL> select * from lbx;
no rows selected
SQL> insert into lbx(id,name) values(1,'a');
1 row created.
SQL> quit
--session2查看
SQL2>select versions_xid as xid,versions_startscn as start_scn,versions_endscn as end_scn,versions_operation as operation,id from lbx versions between scn minvalue and maxvalue where name='a';
XID START_SCN END_SCN O ID
---------------- ---------- ---------- - ----------
0400100025030000 1252595 I 1
正常显示
--使用DDL语句增加列
SQL> update lbx set id=id+10 where name='a';
1 row updated.
SQL> update lbx set id=id+10 where name='a';
1 row updated.
SQL> alter table lbx add(col4 number);
Table altered.
--session2查看
SQL2>select versions_xid as xid,versions_startscn as start_scn,versions_endscn as end_scn,versions_operation as operation,id from lbx versions between scn minvalue and maxvalue where name='a';
XID START_SCN END_SCN O ID
---------------- ---------- ---------- - ----------
03001200C7030000 1252712 U 22
05000D00CF030000 1252678 1252712 I 2
0600130081040000 1252659 D 1
0400100025030000 1252595 1252659 I 1
正常显示每次的操作
--使用DDL删除列
SQL> update lbx set id=id+3 where name='a';
1 row updated.
SQL> update lbx set id=id+3 where name='a';
1 row updated.
SQL> alter table lbx drop column col4;
Table altered.
--session2查看
SQL2>select versions_xid as xid,versions_startscn as start_scn,versions_endscn as end_scn,versions_operation as operation,id from lbx versions between scn minvalue and maxvalue where name='a';
XID START_SCN END_SCN O ID
---------------- ---------- ---------- - ----------
28
历史操作版本都被清空了!!!
整个操作过程中日志没有切换过!一直是使用的日志组1
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
手动切换日志测试过,同样不影响历史操作版本查询.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP# ,SEQUENCE# ,STATUS from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 55 ACTIVE
2 56 CURRENT
3 54 INACTIVE
SQL2>select versions_xid as xid,versions_startscn as start_scn,versions_endscn as end_scn,versions_operation as operation,id from lbx versions between scn minvalue and maxvalue where name='a';
XID START_SCN END_SCN O ID
---------------- ---------- ---------- - ----------
0700160018030000 1253103 U 34
1253103 28
结论: 似乎本题没有合适的答案,疑惑.
扩展:
flashback versions query 和 flashback transaction query有什么区别和联系?