QUESTION NO: 381
Which pseudocolumn could you use to identify a unique row in a Flashback Versions Query? A. XID
B. VERSIONS_PK
C. VERSIONS_XID
D. VERSIONS_UNIQUE
【题目示意】
此题考查有关于Flashback Versions Query的相关知识。
【解析】
Flashback Versions Query是Oracle来检索在一个给定的时间间隔特定行存在的不同版本。每执行一次COMMIT语句创建一行版本。
Oracle Flashback Version Query Row Data Pseudocolumns
Pseudocolumn Name | Description |
Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. If this pseudocolumn is NULL, then the row version was created before start. | |
SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation. | |
Identifier of the transaction that created the row version. | |
Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETEoperation, or the row affected by an UPDATE operation. For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION. |
可以看出VERSIONS_XID是定义事务生成行的版本。
【实验】
1)执行DML操作
SYS@ENMOEDU >create table test (a int);
Table created.
SYS@ENMOEDU >insert into test values(1);
1 row created.
SYS@ENMOEDU >insert into test values(2);
1 row created.
SYS@ENMOEDU >insert into test values(3);
1 row created.
SYS@ENMOEDU >select * from test;
A
----------
1
2
3
SYS@ENMOEDU >commit;
Commit complete.
SYS@ENMOEDU >delete from test where a=1;
1 row deleted.
SYS@ENMOEDU >commit;
Commit complete.
SYS@ENMOEDU >delete from test where a=2;
1 row deleted.
SYS@ENMOEDU >commit;
Commit complete.
SYS@ENMOEDU >select * from test;
A
----------
3
SYS@ENMOEDU >update test set a=4;
1 row updated.
SYS@ENMOEDU >commit;
Commit complete.
SYS@ENMOEDU >select * from test;
A
----------
4
SYS@ENMOEDU >commit;
Commit complete.
2)执行闪回版本查询
SYS@ENMOEDU >select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, a
3 from test versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V A
---------------------------------------------------------------------------
02-FEB-14 07.04.25 PM 0A000F0087020000 U 4
02-FEB-14 07.03.42 PM 03000B0066030000 D 2
02-FEB-14 07.03.24 PM 05001300FF030000 D 1
02-FEB-14 07.02.44 PM 02-FEB-14 07.04.25 PM 04000800CC020000 I 3
02-FEB-14 07.02.44 PM 02-FEB-14 07.03.42 PM 04000800CC020000 I 2
02-FEB-14 07.02.44 PM 02-FEB-14 07.03.24 PM 04000800CC020000 I 1
从VERSIONS_XID可以看出commit一次就会有一个行版本号。
【小结】
VERSIONS_XID就是唯一定义行的,因此选择C
【答案】 C
相关参考
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS622
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29435844/viewspace-1078499/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29435844/viewspace-1078499/