Oracle ORA_ROWSCN 伪列 说明

8lmfp7f10q

　　

一. 官网对该伪列的说明
　　From：11gR2
　　http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/pseudocolumns007.htm#SQLRF50953
　　
　　Foreach row, ORA_ROWSCN returns the conservative upper bound system change number(SCN) of the most recent change to the row in the current session. Thispseudocolumn is useful for determining approximately when a row was lastupdated.

　　Itis not absolutely precise, because Oracle tracks SCNs by transaction committedfor the block in which the row resides. You can obtain a more fine-grainedapproximation of the SCN by creating your tables with row-level dependencytracking. Refer to
CREATE TABLE ...
NOROWDEPENDENCIES| ROWDEPENDENCIES for more information on row-level dependency tracking.
　　
　　 ROWDEPENDENCIESSpecify ROWDEPENDENCIES if you want to enable row-level dependency tracking.This setting is useful primarily to allow for parallel propagation inreplication environments. It increases the size of each
row by 6 bytes.
　　NOROWDEPENDENCIESSpecify NOROWDEPENDENCIES if you do not want table to use the row-leveldependency tracking feature. This is the default.
　　
　　也就是说，使用表的默认创建参数，即norowdependencies时，此时的ora_rawscn 取自data block header的SCN，那么这时候，对于同一个block里的row而言，他们的ora_rowscn 是一样的。

　　而在创建table时指定为rowdependencies时，那么会为每行row 保存一个ora_rowscn. 这样对于同一个block里的row，会有多个ora_rowscn 值。通过dump block，可以发现每个row 会多出一个dscn的信息，该信息就是用来保存ora_rowscn的。
　　
　　如：
　　tab 0, row 1, @0×1f88

tl: 12 fb: –H-FL– lb: 0×2 cc: 1
dscn 0×0000.00000000
　　
　　tab 0, row 1, @0×1f88

tl: 12 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0005.105a6cc1
　　
　　这是同一个row的dump 信息，第一次dscn 为0. 此时信息是从itl的Scn/Fsc中获得的，当itl发生cleanout时会把Scn/Fsc刷到dscn，就是上面的第二段信息。具体这块后面会实验证明。
　　
　　关于block dump 和 itl 说明，参考：
　　
Oracle datafile block 格式 说明
　　http://blog.csdn.net/tianlesoftware/article/details/6654786
　　
　　
Orace ITL(InterestedTransaction List) 说明
　　http://blog.csdn.net/tianlesoftware/article/details/6573988
　　
　　You cannot use this pseudocolumn in a query to a view.However, you can use it to refer to the underlying table when creating a view.You can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETEstatement.
　　--不能在视图使用ora_rowscn伪列
　　
　　ORA_ROWSCN is not supported for Flashback Query.Instead, use the version query pseudocolumns, which are provided explicitly forFlashback Query. Refer to the SELECT ...
flashback_query_clausefor information on Flashback Query and
"VersionQuery Pseudocolumns" for additional information on thosepseudocolumns.
　　--ora_rowscn 不支持Flashbackquery。
　　
　　Restriction on ORA_ROWSCN: This pseudocolumn is notsupported for external tables.
　　--ora_rowscn 不支持外部表
　　
　　Example：
　　Thefirst statement below uses the ORA_ROWSCN pseudocolumn to get the system changenumber of the last operation on the employees table.
　　
　　SELECT ORA_ROWSCN, last_name
　　FROM employees
　　WHERE employee_id = 188;
　　--使用ORA_ROWSCN获取该row 最后一次更新时的SCN
　　
　　The second statement uses the pseudocolumnwith the SCN_TO_TIMESTAMP function to determine the timestamp of the operation:


　　SELECTSCN_TO_TIMESTAMP(ORA_ROWSCN), last_name
　　FROM employees
　　WHERE employee_id = 188;
　　--使用SCN_TO_TIMESTAMP 和 ORA_ROWSCN，获取最后一次修改row的时间
　　

二. ORA_ROWSCN 说明
　　ORA_ROWSCN伪列是Oracle10g引入的，可以查询表中记录最后变更的SCN。这个新的伪列在某些环境下会非常有用，比如执行乐观锁定，或者增量数据抽取的时候。但是，默认情况下，每行记录的ORA_ROWSCN是基于Block的，除非在建表的时候执行开启行级跟踪(createtable … rowdependencies)。
　　

2.1 乐观锁和ORA_ROWSCN
　　

需要select ... for update做悲观锁定的时候，通过使用ORA_ROWSCN可以改成乐观锁定。一开始select数据的时候将ORA_ROWSCN查出来，修改后如果要写回数据库之前再比对下最新的ORA_ROWSCN就可以知道这期间数据是否有发生变化。
　　

2.2 增量数据抽取和ORA_ROWSCN
　　每次抽取后记录最大的ORA_ROWSCN，下次抽取再基于上一次的SCN来获得最近修改过的数据即可。在10g之前，很多系统要实现增量数据抽取，要么通过解析日志，要么加触发器，要么就在表上加一个时间截字段。
　　ORA_ROWSCN其实就是第三种方式，只是这个字段由Oracle来维护，这样可以避免一些应用绕过时间截去更新其他字段带来的问题。不过，如果系统中使用了逻辑备库或者streams等逻辑复制的方案，而数据抽取又是基于逻辑备库的话，ORA_ROWSCN就可能对抽取后的数据分析有影响了，因为通过这个得到的时间是逻辑备库上记录变更的时间，而不是源库的时间了。
　　当然，如果纯粹只是做数据抽取，而不需要使用这个时间来做分析，还是问题不大的，但还是要考虑一旦逻辑备库出现故障需要重做的，则这个增量抽取要怎么来处理的问题。
　　

三. 测试

3.1 基本测试
　　SYS@anqing2(rac2)> create table rowscn1(idnumber,name varchar2(20));
　　Table created.
　　SYS@anqing2(rac2)> insert into rowscn1values(1,'dave');
　　1 row created.
　　SYS@anqing2(rac2)> insert into rowscn1values(1,'dave');
　　1 row created.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> SELECT
　　2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
　　3dbms_rowid.rowid_block_number(rowid) BLOCKNO,
　　dbms_rowid.rowid_row_number(rowid) ROWNO,
　　id,name from rowscn1; 45
　　
　　REL_FNO BLOCKNO ROWNO ID NAME
　　---------- ---------- ---------- ------------------------------
　　1 305890 0 1 dave
　　1 305890 1 1 dave
　　
　　SYS@anqing2(rac2)> select ora_rowscn,idfrom rowscn1;
　　
　　ORA_ROWSCN ID
　　---------- ----------
　　7233799 1
　　7233799 1
　　
　　在默认情况下，使用的是block header的SCN，所以这时候，如果我们就该该表的信息，block header 的scn 发生改变，那么对应block上所有的ora_rowscn 也会发生改变。
　　
　　SYS@anqing2(rac2)> insert into rowscn1values(2,'anqing');
　　1 row created.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> select ora_rowscn,id from rowscn1;
　　ORA_ROWSCN ID
　　---------- ----------
　　7233940 1
　　7233940 1
　　7233940 2
　　
　　这个和我们上面的理论一致。
　　

3.2 rowdependencies/norowdependencies 与ora_rowscn测试
　　
　　SYS@anqing2(rac2)> create tablerowscn2(id number,name varchar2(20)) rowdependencies;
　　Table created.
　　启动行级别的跟踪。
　　
　　SYS@anqing2(rac2)> insert into rowscn2values(1,'dave');
　　1 row created.
　　SYS@anqing2(rac2)> insert into rowscn2values(2,'anqing');
　　1 row created.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　
　　SYS@anqing2(rac2)> SELECT
　　2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
　　3dbms_rowid.rowid_block_number(rowid) BLOCKNO,
　　dbms_rowid.rowid_row_number(rowid) ROWNO,
　　id,name from rowscn2;
　　4 5
　　REL_FNO BLOCKNO ROWNO ID NAME
　　---------- ---------- ---------- ------------------------------
　　1 305898 0 1 dave
　　1 305898 1 2 anqing
　　
　　SYS@anqing2(rac2)> select ora_rowscn,idfrom rowscn2;
　　ORA_ROWSCN ID
　　---------- ----------
　　7234177 1
　　7234177 2
　　
　　测试2个ora_rowscn 是一样的。 我们把这个block dump 出来看一下：
　　
　　SYS@anqing2(rac2)> oradebug setmypid
　　Statement processed.
　　SYS@anqing2(rac2)> alter system dump datafile 1 block 305898;
　　System altered.
　　SYS@anqing2(rac2)> oradebugtracefile_name
　　/u01/app/oracle/admin/anqing/udump/anqing2_ora_22260.trc
　　
　　[oracle@rac2 ~]$ cat/u01/app/oracle/admin/anqing/udump/anqing2_ora_22260.trc
　　/u01/app/oracle/admin/anqing/udump/anqing2_ora_22260.trc
　　Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Production
　　With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
　　and Real Application Testing options
　　ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1
　　System name: Linux
　　Node name: rac2
　　Release: 2.6.18-194.el5
　　Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010
　　Machine: i686
　　Instance name: anqing2
　　Redo thread mounted by this instance: 2
　　Oracle process number: 20
　　Unix process pid: 22260, image: oracle@rac2(TNS V1-V3)
　　
　　*** 2011-08-04 00:35:21.673
　　*** ACTION NAME:() 2011-08-04 00:35:21.673
　　*** MODULE NAME:(sqlplus@rac2 (TNS V1-V3))2011-08-04 00:35:21.673
　　*** SERVICE NAME:(SYS$USERS) 2011-08-0400:35:21.673
　　*** SESSION ID:(148.61626) 2011-08-0400:35:21.673
　　Start dump data blocks tsn: 0 file#: 1minblk 305898 maxblk 305898
　　buffer tsn: 0 rdba: 0x0044aaea (1/305898)
　　scn: 0x0000.006e6281 seq: 0x01 flg: 0x02tail: 0x62810601
　　frmt: 0x02 chkval: 0x0000 type: 0x06=transdata
　　Hex dump of block: st=0, typ_found=1
　　Dump of memory from 0x0EAF6400 to0x0EAF8400
　　.....
　　Block header dump: 0x0044aaea
　　Object id on Block? Y
　　seg/obj: 0xdbdd csc: 0x00.6e627d itc: 2flg: O typ: 1 - DATA
　　fsl: 0 fnx: 0x0 ver: 0x01
　　
　　ItlXid Uba FlagLck Scn/Fsc
　　0x010x000e.004.000003cf0x0140003b.00ce.10 --U- 2 fsc 0x0000.006e6281
　　0x020x0000.000.000000000x00000000.0000.00 ---- 0 fsc 0x0000.00000000
　　
　　data_block_dump,data header at 0xeaf645c
　　===============
　　tsiz: 0x1fa0
　　hsiz: 0x16
　　pbl: 0x0eaf645c
　　bdba: 0x0044aaea
　　76543210
　　flag=--R-----
　　ntab=1
　　nrow=2
　　frre=-1
　　fsbo=0x16
　　fseo=0x1f7c
　　avsp=0x1f66
　　tosp=0x1f66
　　0xe:pti[0] nrow=2offs=0
　　0x12:pri[0] offs=0x1f8f
　　0x14:pri[1] offs=0x1f7c
　　block_row_dump:
　　tab 0, row 0, @0x1f8f
　　tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
　　dscn 0x0000.00000000
　　col0: [ 2] c1 02
　　col1: [ 4] 64 61 76 65
　　tab 0, row 1, @0x1f7c
　　tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
　　dscn 0x0000.00000000
　　col0: [ 2] c1 03
　　col1: [ 6] 61 6e 71 69 6e 67
　　end_of_block_dump
　　End dump data blocks tsn: 0 file#: 1 minblk305898 maxblk 305898
　　
　　 此时测dscn 为0. 在前面讲过， ora_rwscn 是在itl 发生cleanout 时刷到dscn的。 我们在进行一些事务操作查看一下。
　　
　　SYS@anqing2(rac2)> select * fromrowscn2;
　　ID NAME
　　---------- --------------------
　　1 dave
　　2 anqing
　　
　　SYS@anqing2(rac2)> update rowscn2 setid=3 where id=1;
　　1 row updated.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> update rowscn2 setid=4 where id=2;
　　1 row updated.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> select ora_rowscn,idfrom rowscn2;
　　ORA_ROWSCN ID
　　---------- ----------
　　7234500 3
　　7234509 4
　　--测试的ora_rowscn已经发生改变
　　
　　我们dump 看一下：
　　Itl Xid Uba FlagLck Scn/Fsc
　　0x010x000c.005.0000042a0x01400101.016d.34 --U- 1fsc 0x0000.006e63cd
　　0x020x000e.006.000003cb0x0140003b.00ce.11 --U- 1 fsc0x0000.006e63c4
　　
　　block_row_dump:
　　tab 0, row 0, @0x1f8f
　　tl: 17 fb: --H-FL-- lb: 0x2 cc: 2
　　dscn 0x0000.006e6281
　　col0: [ 2] c1 04
　　col1: [ 4] 64 61 76 65
　　tab 0, row 1, @0x1f7c
　　tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
　　dscn 0x0000.006e6281
　　col0: [ 2] c1 05
　　col1: [ 6] 61 6e 71 69 6e 67
　　
　　注意这里的dscn 还是一样的，这就是说，我们刚才通过select 查询的结果一个是从dscn出来的，还有一个是从itl里出来的。 我们在进行一些事务操作。
　　
　　SYS@anqing2(rac2)> insert into rowscn2values(1,'huaining');
　　1 row created.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> insert into rowscn2values(2,'dmm');
　　1 row created.
　　SYS@anqing2(rac2)> commit;
　　Commit complete.
　　SYS@anqing2(rac2)> select ora_rowscn,idfrom rowscn2;
　　ORA_ROWSCN ID
　　---------- ----------
　　7234500 3
　　7234509 4
　　7234673 1
　　7234683 2
　　
　　再次dump block：
　　
　　Itl Xid Uba FlagLck Scn/Fsc
　　0x010x000b.02d.000003d40x01400042.00c0.16 --U- 1fsc 0x0000.006e647b
　　0x020x0013.003.000003b40x0140008f.00c2.12 --U- 1fsc 0x0000.006e6471
　　
　　block_row_dump:
　　tab 0, row 0, @0x1f8f
　　tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
　　dscn 0x0000.006e63c4
　　col0: [ 2] c1 04
　　col1: [ 4] 64 61 76 65
　　tab 0, row 1, @0x1f7c
　　tl: 19 fb: --H-FL-- lb: 0x0 cc: 2
　　dscn 0x0000.006e63cd
　　col0: [ 2] c1 05
　　col1: [ 6] 61 6e 71 69 6e 67
　　tab 0, row 2, @0x1f67
　　tl: 21 fb: --H-FL-- lb: 0x2 cc: 2
　　dscn 0x0000.00000000
　　col0: [ 2] c1 02
　　col1: [ 8] 68 75 61 69 6e 69 6e 67
　　tab 0, row 3, @0x1f57
　　tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
　　dscn 0x0000.00000000
　　col0: [ 2] c1 03
　　col1: [ 3] 64 6d 6d
　　end_of_block_dump
　　
　　--测试我们的前两条记录的dscn 值不一样了。
　　
　　
　　最后执行一下官网有关时间的一个测试：
　　
　　SYS@anqing2(rac2)> col SCN_TO_TIMESTAMP(ORA_ROWSCN)for a35
　　SYS@anqing2(rac2)> selectscn_to_timestamp(ora_rowscn),id,name from rowscn2;
　　
　　SCN_TO_TIMESTAMP(ORA_ROWSCN) ID NAME
　　--------------------------------------------- --------------------
　　04-AUG-11 12.39.42.000000000 AM 3 dave
　　04-AUG-11 12.39.54.000000000 AM 4 anqing
　　04-AUG-11 12.44.27.000000000 AM 1 huaining
　　04-AUG-11 12.44.39.000000000 AM 2 dmm
　　
　　
　　
　　
　　
　　-------------------------------------------------------------------------------------------------------
　　Blog： http://blog.csdn.net/tianlesoftware
　　Weibo: http://weibo.com/tianlesoftware
　　Email: dvd.dba@gmail.com
　　DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)
　　DBA 超级群：63306533(满); DBA4 群： 83829929(满)DBA5群： 142216823(满)
　　DBA6 群：158654907(满) 聊天 群：40132017(满) 聊天2群：69087192(满)
　　--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请