ora_rowscn 与 rowdependencies（10g）

        ora_rowscn是Oracle 10g 新增加的，是记录最后一次被修改时的scn。Flashback version query通过该伪列跟踪记录的历史变化。

       ora_rowscn缺省是数据块级别的，也就是一个数据块内的所有记录都是一个ora_rowscn,数据块内任意一条记录被修改，这个数据块的所有记录的ora_rowscn都同时改变。

eg：

SQL> create table test (
  2    id number(3),
  3    name varchar2(100)
  4  );
 
Table created
 
SQL> insert into test values(1,'dep1');
 
1 row inserted
  
SQL> insert into test values(2,'dep2');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select ora_rowscn,t.* from test t;
 
ORA_ROWSCN   ID NAME
---------- ---- --------------------------------------------------------------------------------
    560015    1 dep1
    560015    2 dep2

 
SQL> update test set name='dep1_1' where id = 1;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select ora_rowscn,t.* from test t;
 
ORA_ROWSCN   ID NAME
---------- ---- --------------------------------------------------------------------------------
    560038    1 dep1_1
     560038    2 dep2                                         --注意 id＝2的记录的ora_rowscn变了

 

看看数据的历史变动情况

SQL> select id,
  2         name,
  3         versions_xid,
  4         versions_startscn,
  5         versions_endscn,
  6         versions_operation
  7    from test versions between scn minvalue and maxvalue
  8   where id = 1
  9   order by versions_startscn
 10  ;
 
  ID NAME    VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION
---- ------- ---------------- ----------------- --------------- ------------------
   1 dep1    08002C0052010000            560015          560038 I
   1 dep1_1  0300220048010000            560038                 U
 

SQL> select id,
  2         name,
  3         versions_xid,
  4         versions_startscn,
  5         versions_endscn,
  6         versions_operation
  7    from test versions between scn minvalue and maxvalue
  8   where id = 2
  9   order by versions_startscn
 10  ;
 
  ID NAME  VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION
---- ----- ---------------- ----------------- --------------- ------------------
   2 dep2  08002C0052010000            560015                 I

 

现在看看使用rowdependencies改变这种情况：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as dlt
 
SQL>
SQL> create table test2 (
  2    id number(3),
  3    name varchar2(100)
  4  ) rowdependencies;
 
Table created
 
SQL> insert into test2 select * from test;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select ora_rowscn,dbms_rowid.rowid_block_number(t.rowid),t.* from test2 t;
 
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(   ID NAME
---------- ------------------------------ ---- --------------------------------------------------------------------------------
    560674                             20    1 dep1_1
    560674                             20    2 dep2
 
SQL> update test2 set name='dep2_1' where id =2;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select ora_rowscn,dbms_rowid.rowid_block_number(t.rowid),t.* from test2 t;
 
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(   ID NAME
---------- ------------------------------ ---- --------------------------------------------------------------------------------
     560674                             20    1 dep1_1
    560722                             20    2 dep2_1
 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-705432/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-705432/