oracle 查询表中数据行(row)上最后的DML时间

在这介绍oracle 10G开始提供的一个伪列ORA_ROWSCN,它又分为两种模式一种是基于block这是默认的模式(块级跟踪)；还有一种是基于row上，这种模式只能在建里表时指定ROWDEPENDENCIES(行级跟踪)，不可以通过后期的alter  table语句来将表修改为ROWDEPENDENCIES。

我们知道，每个Block在头部是记录了该block最近事务的SCN的，所以默认情况下，只需要从block头部直接获取这个值就可以了，不需要其他任何的开销，Oracle就能做到这一点。但是这明显是不精确的，一个block中会有很多行记录，每次事务不可能影响到整个block中所有的行，所以这是一个非常不精准的估算值，同一个block的所有记录的ORA_ROWSCN都会是相同的，基本上没有多大的使用价值。

如果在建表的时候开启行级跟踪选项，Oracle则可以为每一行记录精确的SCN，那么显然不能再直接从block头部获取。要获得足够的信息，肯定要付出一定的代价，Oracle必须为每一行存储这个实际的SCN。所以这个行级跟踪的选项，只能在建表的时候指定，而不能通过alter table来修改现有的表，否则需要修改每一行记录的物理存储格式，代价是可想而知的。

在10g之前，很多系统要实现增量数据抽取，要么通过解析日志，要么加触发器，要么就在表上加一个时间截字段。ORA_ROWSCN其实就是第三种方式，只是这个字段由Oracle来维护，这样可以避免一些应用绕过时间截去更新其他字段带来的问题。

下面做一个实验来证明，首先在默认状态下修改同一个块上的其中一条数据，然后再启用行级跟踪，修改同一块上的一条数据，观察ora_rowscn变化。

第一种方式（块级跟踪）：

select ora_rowscn,
       dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blockid,
       scn_to_timestamp(ora_rowscn)
  from hs_futures.fuentrust t
orderby scn_to_timestamp(ora_rowscn);

dbms_rowid.ROWID_BLOCK_NUMBER(rowid)：是为获取数据所在块的ID

scn_to_timestamp(ora_rowscn)：获取数据最所修改的时间

数据发现变化后通过上面SQL语句可以查看到数据最后修改的时间，注意因为是同一个块上，所以这个块上只要有DML操作那么所有数据的scn都更新了，所以凡是跟这条记录在同一个块上数据获取到的ora_rowscn和scn_to_timestamp(ora_rowscn)两个值都发生了相应的变化。

第二种方式（行级跟踪）：

createtable hs_futures.fuentrust_test1 rowdependenciesas
  select * from hs_futures.fuentrust

用以上语句创建一个基于ROWDEPENDENCIES模式的表，然后用第一种方法中的SQL去查询此表数据中的ora_rowscn，scn_to_timestamp(ora_rowscn)两个值，修改其中的某一条记录然后再去查询那个值发现发生变化的只是被修改那条记录的这两个值发生了变化，而在同一个块中没有被修改的其它记录这两个值是不会产生变化的。

注意DDL操作：只要现有表记录中的数据有发生变化那么SCN肯定就会发生更新，如删除有数据的列，但是如果索引删除/修改/增加及增加/者删除没有任何数据的列那么SCN是不会有任何变化。

 

下面是测试：

默认表中修改块中的记录会影响本块其他记录的 ora_rowscn
如果加上ROWDEPENDENCIES ，那么修改记录只影响本记录的ora_rowscn，不会引起其他记录的改变
加上这个功能，会影响存储，每条记录会增加6字节的物理存储空间
sample
默认情况下
T@ora>create table t1 as select * from t ;

Table created.

Elapsed: 00:00:00.07
T@ora>select rowid,ora_rowscn,a from t1;

ROWID              ORA_ROWSCN          A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA   45387504          1
AAARN5AAGAAAXHcAAB   45387504          2
AAARN5AAGAAAXHcAAC   45387504          3
AAARN5AAGAAAXHcAAD   45387504          4
AAARN5AAGAAAXHcAAE   45387504          5
AAARN5AAGAAAXHcAAF   45387504          6
AAARN5AAGAAAXHcAAG   45387504          7
AAARN5AAGAAAXHcAAH   45387504          8
AAARN5AAGAAAXHcAAI   45387504          9
AAARN5AAGAAAXHcAAJ   45387504         10

10 rows selected.

Elapsed: 00:00:00.20
T@ora>update t1 set a = 0 where a <3;

2 rows updated.

Elapsed: 00:00:00.01
T@ora>commit;

Commit complete.

Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t1;

ROWID              ORA_ROWSCN          A
------------------ ---------- ----------
AAARN5AAGAAAXHcAAA   45387522          0
AAARN5AAGAAAXHcAAB   45387522          0
AAARN5AAGAAAXHcAAC   45387522          3 《----这里开始后面的记录都没做修改，但是ora_rowscn却改变了
AAARN5AAGAAAXHcAAD   45387522          4
AAARN5AAGAAAXHcAAE   45387522          5
AAARN5AAGAAAXHcAAF   45387522          6
AAARN5AAGAAAXHcAAG   45387522          7
AAARN5AAGAAAXHcAAH   45387522          8
AAARN5AAGAAAXHcAAI   45387522          9
AAARN5AAGAAAXHcAAJ   45387522         10

10 rows selected.
增加ROWDEPENDENCIES
T@ora>create table t2 ROWDEPENDENCIES  as select * from t ;

Table created.

Elapsed: 00:00:00.06
T@ora>select rowid,ora_rowscn,a from t2;

ROWID              ORA_ROWSCN          A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA   45387561          1
AAARN6AAGAAAXHkAAB   45387561          2
AAARN6AAGAAAXHkAAC   45387561          3
AAARN6AAGAAAXHkAAD   45387561          4
AAARN6AAGAAAXHkAAE   45387561          5
AAARN6AAGAAAXHkAAF   45387561          6
AAARN6AAGAAAXHkAAG   45387561          7
AAARN6AAGAAAXHkAAH   45387561          8
AAARN6AAGAAAXHkAAI   45387561          9
AAARN6AAGAAAXHkAAJ   45387561         10

10 rows selected.

Elapsed: 00:00:00.03
T@ora>update t2 set a = 0 where a <3;

2 rows updated.

Elapsed: 00:00:00.01
T@ora>commit;

Commit complete.

Elapsed: 00:00:00.00
T@ora>select rowid,ora_rowscn,a from t2;

ROWID              ORA_ROWSCN          A
------------------ ---------- ----------
AAARN6AAGAAAXHkAAA   45387578          0
AAARN6AAGAAAXHkAAB   45387578          0
AAARN6AAGAAAXHkAAC   45387561          3 《--没有修改的scn就不会改变
AAARN6AAGAAAXHkAAD   45387561          4
AAARN6AAGAAAXHkAAE   45387561          5
AAARN6AAGAAAXHkAAF   45387561          6
AAARN6AAGAAAXHkAAG   45387561          7
AAARN6AAGAAAXHkAAH   45387561          8
AAARN6AAGAAAXHkAAI   45387561          9
AAARN6AAGAAAXHkAAJ   45387561         10

存储空间的影响，dump出来就可以看到2个存储结构不一样
普通表
tab 0, row 0, @0x1f1e
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 1]  80
col  1: [ 2]  c1 02
2个number类型
tab 0, row 0, @0x1edc
tl: 14 fb: --H-FL-- lb: 0x2  cc: 2
dscn 0x0000.02b48f29
col  0: [ 1]  80
col  1: [ 2]  c1 02

多出6个字节记录dscn