ORA_ROWSCN
Flashback Query
Timestamp or SCN
Version
SQL> create table t as select rownum as x,rownum as y from dual connect by rownum<=3;
通过rowid解析block number和row number，看出这三条数据的block number相同，说明都在同一个data block里面
SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t;
BLK RNO ORA_ROWSCN X Y
---------- ---------- ---------- ---------- ----------
89512 0 562061685 1 1
89512 1 562061685 2 2
89512 2 562061685 3 3
ORA_ROWSCN
默认情况下ORA_ROWSCN纪录的是一个data block最后被修改的scn，所以即使只修改一行数据，整个block里面的每行都会反映出来。这个伪列在处理乐观锁定的时候很有用处。
SQL> update t set y=1 where x=1;
仅仅update而不做commit的时候，rowscn不会变化，仍然是562061685
SQL> select ora_rowscn,x,y from t;
ORA_ROWSCN X Y
---------- ---------- ----------
562061685 1 1
562061685 2 2
562061685 3 3
SQL> commit;
Commit complete.
commit之后再次查询，可以看到整个block都发生了变化
SQL> select ora_rowscn,x,y from t;
ORA_ROWSCN X Y
---------- ---------- ----------
562327939 1 1
562327939 2 2
562327939 3 3
如果在建表的时候使用 rowdependencies 属性，ORA_ROW_SCN就会记录每行最后修改的scn。使用这个属性，每行会增加6byte的存储空间。
SQL> create table t2 rowdependencies as select rownum as x,rownum as y from dual connect by rownum<=3;
Table created.
SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t2;
BLK RNO ORA_ROWSCN X Y
---------- ---------- ---------- ---------- ----------
176216 0 569148721 1 1
176216 1 569148721 2 2
176216 2 569148721 3 3
SQL> update t2 set y=1 where x=1;
1 row updated.
SQL> commit;
Commit complete.
虽然三行属于同一个block，但是没行的ora_rowscn却不同。
SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t2;
BLK RNO ORA_ROWSCN X Y
---------- ---------- ---------- ---------- ----------
176216 0 569149392 1 1
176216 1 569148721 2 2
176216 2 569148721 3 3
Flashback Query by Timestamp or SCN
在比较短的时间里，多次对一条数据进行修改，并且纪录下每次修改前的date和scn
SQL> update t set y=0 where x=1;
SQL> commit;
SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS SCN
----------------------- ----------
20080520 20:15:44.72944 562860704
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.
SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS SCN
----------------------- ----------
20080520 20:16:15.72975 562869199
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.
SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS SCN
----------------------- ----------
20080520 20:16:24.72984 562872846
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.
有了这些date和scn之后，就可以使用flashback query来查询某个时间点的数据
SQL> select ora_rowscn,x,y from t as of timestamp to_timestamp('20080520 20:15:40','yyyymmdd hh24:mi:ss');
ORA_ROWSCN X Y
---------- ---------- ----------
562857027 1 0
562857027 2 2
562857027 3 3
ORACLE把这个指定的timestamp转换成scn进行查询。我们也可以使用函数 timestamp_to_scn() 和 scn_to_timestamp() 来进行转换。
sys.smon_scn_time 这个表纪录了这个对应关系。官方文档说明，这个表每5分钟更新一次，如果找不到精确的匹配，就采用四舍五入找到最接近的scn。（但是使用上面纪录的时间，都找到了精确的匹配，所以对这个5分钟的理解还不太明白？不过可以肯定的是，使用scn一定可以找到精确的值）
SQL> select * from (select to_char(time_dp,'yyyymmdd hh24:mi:ss') time_dp,scn from sys.smon_scn_time order by 1 desc) where rownum<=5;
TIME_DP SCN
----------------- ----------
20080520 20:25:56 563045354
20080520 20:20:53 562958208
20080520 20:15:09 562852192
20080520 20:09:58 562754524
20080520 20:03:59 562639175
TIMESTAMP_TO_SCN
另外做一个测试，在更短的时间内修改数据，证明使用timestamp的确不能精确反映scn，但是没有仍然找到这个时间间隔是多少。
create table t1(x int, y int, t timestamp(9));
insert into t1(x,y,t) values(1,0,systimestamp);
commit;
-- 这个代码能够返回每次update时刻的timestamp，和转换得到的scn
declare
l_t timestamp(9);
begin
update t1 set y = 0;
commit;
for i in 1 .. 5 loop
update t1 set y = y + 1, t = systimestamp returning t into l_t;
commit;
dbms_output.put_line(to_char(l_t)||' ---- '||timestamp_to_scn(l_t));
dbms_lock.sleep(2);
end loop;
end;
/
运行结果如下。代码中进行了5次commit，每次间隔1秒。我们知道至少应该有5个不同的scn，但是通过转换仅仅得到2个不同的scn
20-MAY-08 09.47.03.738067 PM ---- 564380246
20-MAY-08 09.47.04.740171 PM ---- 564380246
20-MAY-08 09.47.05.742007 PM ---- 564380246
20-MAY-08 09.47.06.743822 PM ---- 564381615
20-MAY-08 09.47.07.745620 PM ---- 564381615
间隔时间2秒，得到了3个不同的scn
20-MAY-08 09.53.53.547296 PM ---- 564516228
20-MAY-08 09.53.55.548386 PM ---- 564517232
20-MAY-08 09.53.57.550024 PM ---- 564517232
20-MAY-08 09.53.59.551694 PM ---- 564518469
20-MAY-08 09.54.01.553324 PM ---- 564518469
间隔时间5秒，得到了5个不同的scn
20-MAY-08 09.49.06.384516 PM ---- 564421038
20-MAY-08 09.49.11.385069 PM ---- 564421874
20-MAY-08 09.49.16.386223 PM ---- 564423813
20-MAY-08 09.49.21.387317 PM ---- 564425644
20-MAY-08 09.49.26.388466 PM ---- 564427012
flashback version query
如果在更新数据的时候没有记下time或者scn，又想知道某行数据或表的更新历史，就需要使用flashback version query功能
select versions_starttime, versions_endtime, versions_operation, x, y
from t versions between timestamp minvalue and maxvalue
where versions_starttime >
to_timestamp('20080520 20:15:40', 'yyyymmdd hh24:mi:ss');
另外一个伪列 xid 表示transaction id，可以用来查询视图 flashback_transaction_query 或者 v$logmnr_contents
SQL> /
VERSIONS_STARTTIME VERSIONS_ENDTIME V X Y
------------------------------ ------------------------------ - ---------- ----------
20-MAY-08 08.16.27 PM U 1 3
20-MAY-08 08.16.18 PM 20-MAY-08 08.16.27 PM U 1 2
20-MAY-08 08.15.53 PM 20-MAY-08 08.16.18 PM U 1 1