oracle ora rowscn,Oracle 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

2 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3 dbms_rowid.rowid_block_number(rowid) BLOCKNO,

dbms_rowid.rowid_row_number(rowid) ROWNO,

id,name from rowscn1;  4   5

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

2 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3 dbms_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: 2 flg: O  typ: 1 - DATA

fsl: 0  fnx: 0x0 ver: 0x01

Itl          Xid                  Uba         Flag Lck        Scn/Fsc

0x01  0x000e.004.000003cf 0x0140003b.00ce.10  --U-    2  fsc 0x0000.006e6281

0x02  0x0000.000.00000000 0x00000000.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=2 offs=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

col 0: [ 2]  c1 02

col 1: [ 4]  64 61 76 65

tab 0, row 1, @0x1f7c

tl: 19 fb: --H-FL-- lb: 0x1  cc: 2

dscn 0x0000.00000000

col 0: [ 2]  c1 03

col 1: [ 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的。 我们在进行一些事务操作查看一下。