053-704

704. View the Exhibit and examine the data manipulation language (DML) operations that you performed 
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有什么区别和联系?