SQL Server : RowVersion equivalent in Oracle

Question

Oracle 是否有与 SQL Server's RowVersion 类似的数据类型？

当您插入或更新行时，相应的版本列(类型为 RowVersion)会自动更新。

MSDN says about RowVersion :

• Is a data type that exposes automatically generated, unique binarynumbers within a database. rowversion is generally used as a mechanismfor version-stamping table rows. The storage size is 8 bytes. Therowversion data type is just an incrementing number and does notpreserve a date or a time.

• Each database has a counter that is incremented for each insert orupdate operation that is performed on a table that contains arowversion column within the database. This counter is the databaserowversion. This tracks a relative time within a database, not anactual time that can be associated with a clock. A table can have onlyone rowversion column. Every time that a row with a rowversion columnis modified or inserted, the incremented database rowversion value isinserted in the rowversion column.

• You can use the rowversion column of a row to easily determine whetherany value in the row has changed since the last time it was read. Ifany change is made to the row, the rowversion value is updated. If nochange is made to the row, the rowversion value is the same as when itwas previously read.

• You can add a rowversion column to a table to help maintain theintegrity of the database when multiple users are updating rows at thesame time. You may also want to know how many rows and which rows wereupdated without re-querying the table.

我们正在使用 Oracle 设计数据模型，并希望使用 Version 列来管理并发性。

我也想知道Oracle世界里是否有更好的方法。

Answer 1

Oracle 有 SCN(系统更改编号):http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#CNCPT039

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.

Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.

使用 ORA_ROWSCN 伪列检查行的当前 SCN:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm#SQLRF51145

一个例子:

SELECT ora_rowscn, t.* From test t;

演示 --> http://www.sqlfiddle.com/#!4/535bc/1
(在 SQLFiddle 上，显式提交显然不起作用 - 在真实数据库上，每次提交都会增加 SCN)。


“真实”数据库的示例:

CREATE TABLE test(
  id int,
  value int
);

INSERT INTO test VALUES(1,0);
COMMIT;
SELECT ora_rowscn, t.* FROM test t;

ORA_ROWSCN         ID      VALUE
---------- ---------- ----------
   3160728          1          0

UPDATE test SET value = value + 1 WHERE id = 1;
COMMIT;
SELECT ora_rowscn, t.* FROM test t;

ORA_ROWSCN         ID      VALUE
---------- ---------- ----------
   3161657          1          1

UPDATE test SET value = value + 1 WHERE id = 1;
COMMIT;
SELECT ora_rowscn, t.* FROM test t;

ORA_ROWSCN         ID      VALUE
---------- ---------- ----------
   3161695          1          2 

如果知道事务的SCN，我们可以使用闪回查询来获取该行的过去值:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131

示例:

SELECT t.*,
       versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation
FROM test VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE t;

        ID      VALUE VERSIONS_STARTSCN VERSIONS_STARTTIME  VERSIONS_ENDSCN VERSIONS_ENDTIME    VERSIONS_XID     VERSIONS_OPERATION
---------- ---------- ----------------- ------------------- --------------- ------------------- ---------------- ------------------
         1          2           3161695 13/12/10 08:19:39                                       06000300EA070000 U                  
         1          1           3161657 13/12/10 08:18:39           3161695 13/12/10 08:19:39   06001200EA070000 U                  
         1          0                                               3161657 13/12/10 08:18:39                         


SELECT t.*,
       versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation
FROM test VERSIONS BETWEEN SCN 3161657 AND 3161657 t;

        ID      VALUE VERSIONS_STARTSCN VERSIONS_STARTTIME  VERSIONS_ENDSCN VERSIONS_ENDTIME    VERSIONS_XID     VERSIONS_OPERATION
---------- ---------- ----------------- ------------------- --------------- ------------------- ---------------- ------------------
         1          1           3161657 13/12/10 08:18:39                                       06001200EA070000 U