这是一个非常重要的特性。从oracle10g开始，oracle在表上引入了一个伪列ORA_ROWSCN。该列记录了每一列最后更改的SCN。但是有两种模式，一种是默认的是data block级别，另一种是row级别，需要在建立表的时候指定ROWDEPENDENCIES，而且不能在表创建后用alter table语句去更改。
我们知道默认情况下SCN存储在data block的头部。这里记载的是该data block的最新更改的SCN。所以默认情况下，你去查一个表的ORA_ROWSCN,同数据块的值是相同的。如下:
SQL> create table test (id number,val char(2000)); SQL> insert into test(id , val) select rownum,object_name from dba_objects where rownum<20; SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618028 08-AUG-14 03.40.58.000000000 PM 2 60754 618028 08-AUG-14 03.40.58.000000000 PM 3 60754 618028 08-AUG-14 03.40.58.000000000 PM 4 60755 618028 08-AUG-14 03.40.58.000000000 PM 5 60755 618028 08-AUG-14 03.40.58.000000000 PM 6 60755 618028 08-AUG-14 03.40.58.000000000 PM 7 60756 618028 08-AUG-14 03.40.58.000000000 PM 8 60756 618028 08-AUG-14 03.40.58.000000000 PM 9 60756 618028 08-AUG-14 03.40.58.000000000 PM 10 60757 618028 08-AUG-14 03.40.58.000000000 PM 11 60757 618028 08-AUG-14 03.40.58.000000000 PM 12 60757 618028 08-AUG-14 03.40.58.000000000 PM 13 60758 618028 08-AUG-14 03.40.58.000000000 PM 14 60758 618028 08-AUG-14 03.40.58.000000000 PM 15 60758 618028 08-AUG-14 03.40.58.000000000 PM 16 60759 618028 08-AUG-14 03.40.58.000000000 PM 17 60759 618028 08-AUG-14 03.40.58.000000000 PM 18 60759 618028 08-AUG-14 03.40.58.000000000 PM 19 60760 618028 08-AUG-14 03.40.58.000000000 PM
上面是准备工作，创建一个表，该表有多个数据块，接下来我们把id=18这一列update看一下结果。
SQL> update test set id=118 where id=18; 1 row updated. SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618028 08-AUG-14 03.40.58.000000000 PM 2 60754 618028 08-AUG-14 03.40.58.000000000 PM 3 60754 618028 08-AUG-14 03.40.58.000000000 PM 4 60755 618028 08-AUG-14 03.40.58.000000000 PM 5 60755 618028 08-AUG-14 03.40.58.000000000 PM 6 60755 618028 08-AUG-14 03.40.58.000000000 PM 7 60756 618028 08-AUG-14 03.40.58.000000000 PM 8 60756 618028 08-AUG-14 03.40.58.000000000 PM 9 60756 618028 08-AUG-14 03.40.58.000000000 PM 10 60757 618028 08-AUG-14 03.40.58.000000000 PM 11 60757 618028 08-AUG-14 03.40.58.000000000 PM 12 60757 618028 08-AUG-14 03.40.58.000000000 PM 13 60758 618028 08-AUG-14 03.40.58.000000000 PM 14 60758 618028 08-AUG-14 03.40.58.000000000 PM 15 60758 618028 08-AUG-14 03.40.58.000000000 PM 16 60759 618028 08-AUG-14 03.40.58.000000000 PM 17 60759 618028 08-AUG-14 03.40.58.000000000 PM 118 60759 618028 08-AUG-14 03.40.58.000000000 PM 19 60760 618028 08-AUG-14 03.40.58.000000000 PM 19 rows selected.
现在还没有commit。但是按照猜想这60759这个数据块对应的列的SCN都应该变了。不过实际没有变，不知道为什么，需要再研究，不过我们commit一下就会变了。
SQL> commit; Commit complete. SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618028 08-AUG-14 03.40.58.000000000 PM 2 60754 618028 08-AUG-14 03.40.58.000000000 PM 3 60754 618028 08-AUG-14 03.40.58.000000000 PM 4 60755 618028 08-AUG-14 03.40.58.000000000 PM 5 60755 618028 08-AUG-14 03.40.58.000000000 PM 6 60755 618028 08-AUG-14 03.40.58.000000000 PM 7 60756 618028 08-AUG-14 03.40.58.000000000 PM 8 60756 618028 08-AUG-14 03.40.58.000000000 PM 9 60756 618028 08-AUG-14 03.40.58.000000000 PM 10 60757 618028 08-AUG-14 03.40.58.000000000 PM 11 60757 618028 08-AUG-14 03.40.58.000000000 PM 12 60757 618028 08-AUG-14 03.40.58.000000000 PM 13 60758 618028 08-AUG-14 03.40.58.000000000 PM 14 60758 618028 08-AUG-14 03.40.58.000000000 PM 15 60758 618028 08-AUG-14 03.40.58.000000000 PM 16 60759 618251 08-AUG-14 03.45.28.000000000 PM 17 60759 618251 08-AUG-14 03.45.28.000000000 PM 118 60759 618251 08-AUG-14 03.45.28.000000000 PM 19 60760 618028 08-AUG-14 03.40.58.000000000 PM
我们再看一下row级别的。
SQL> create table test (id number,val char(2000)) rowdependencies ; Table created. SQL> insert into test(id , val) select rownum,object_name from dba_objects where rownum<20; 19 rows created. SQL> commit; Commit complete. SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618618 08-AUG-14 03.57.40.000000000 PM 2 60754 618618 08-AUG-14 03.57.40.000000000 PM 3 60754 618618 08-AUG-14 03.57.40.000000000 PM 4 60755 618618 08-AUG-14 03.57.40.000000000 PM 5 60755 618618 08-AUG-14 03.57.40.000000000 PM 6 60755 618618 08-AUG-14 03.57.40.000000000 PM 7 60756 618618 08-AUG-14 03.57.40.000000000 PM 8 60756 618618 08-AUG-14 03.57.40.000000000 PM 9 60756 618618 08-AUG-14 03.57.40.000000000 PM 10 60757 618618 08-AUG-14 03.57.40.000000000 PM 11 60757 618618 08-AUG-14 03.57.40.000000000 PM 12 60757 618618 08-AUG-14 03.57.40.000000000 PM 13 60758 618618 08-AUG-14 03.57.40.000000000 PM 14 60758 618618 08-AUG-14 03.57.40.000000000 PM 15 60758 618618 08-AUG-14 03.57.40.000000000 PM 16 60759 618618 08-AUG-14 03.57.40.000000000 PM 17 60759 618618 08-AUG-14 03.57.40.000000000 PM 18 60759 618618 08-AUG-14 03.57.40.000000000 PM 19 60760 618618 08-AUG-14 03.57.40.000000000 PM 19 rows selected. SQL> update test set id=888 where id=18; 1 row updated. SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618618 08-AUG-14 03.57.40.000000000 PM 2 60754 618618 08-AUG-14 03.57.40.000000000 PM 3 60754 618618 08-AUG-14 03.57.40.000000000 PM 4 60755 618618 08-AUG-14 03.57.40.000000000 PM 5 60755 618618 08-AUG-14 03.57.40.000000000 PM 6 60755 618618 08-AUG-14 03.57.40.000000000 PM 7 60756 618618 08-AUG-14 03.57.40.000000000 PM 8 60756 618618 08-AUG-14 03.57.40.000000000 PM 9 60756 618618 08-AUG-14 03.57.40.000000000 PM 10 60757 618618 08-AUG-14 03.57.40.000000000 PM 11 60757 618618 08-AUG-14 03.57.40.000000000 PM 12 60757 618618 08-AUG-14 03.57.40.000000000 PM 13 60758 618618 08-AUG-14 03.57.40.000000000 PM 14 60758 618618 08-AUG-14 03.57.40.000000000 PM 15 60758 618618 08-AUG-14 03.57.40.000000000 PM ERROR: ORA-01405: fetched column value is NULL 15 rows selected. SQL> commit; Commit complete. SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test; ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ------------------------------------ ---------- --------------------------------------------------------------------------- 1 60754 618618 08-AUG-14 03.57.40.000000000 PM 2 60754 618618 08-AUG-14 03.57.40.000000000 PM 3 60754 618618 08-AUG-14 03.57.40.000000000 PM 4 60755 618618 08-AUG-14 03.57.40.000000000 PM 5 60755 618618 08-AUG-14 03.57.40.000000000 PM 6 60755 618618 08-AUG-14 03.57.40.000000000 PM 7 60756 618618 08-AUG-14 03.57.40.000000000 PM 8 60756 618618 08-AUG-14 03.57.40.000000000 PM 9 60756 618618 08-AUG-14 03.57.40.000000000 PM 10 60757 618618 08-AUG-14 03.57.40.000000000 PM 11 60757 618618 08-AUG-14 03.57.40.000000000 PM 12 60757 618618 08-AUG-14 03.57.40.000000000 PM 13 60758 618618 08-AUG-14 03.57.40.000000000 PM 14 60758 618618 08-AUG-14 03.57.40.000000000 PM 15 60758 618618 08-AUG-14 03.57.40.000000000 PM 16 60759 618618 08-AUG-14 03.57.40.000000000 PM 17 60759 618618 08-AUG-14 03.57.40.000000000 PM 888 60759 618643 08-AUG-14 03.58.28.000000000 PM 19 60760 618618 08-AUG-14 03.57.40.000000000 PM 19 rows selected.
先创建一个表，指定rowdependencies 然后插入数值。
我们先更新了一列，没commit，然后去select。有意思的是这里出了个错误，很值得研究。
然后我们commit后发现这一列的更改时间知道了。