ora_rowscn

一、默认情况下
–创建t_orascn测试表
SQL> create table t_orascn(id number);

Table created

–插入两条数据
SQL> insert into t_orascn values(1);

1 row inserted

SQL> insert into t_orascn values(2);

1 row inserted

SQL> commit;

Commit complete

–查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;

ORA_ROWSCN ID
———- ———-
559036 1
559036 2

–更新其中一条数据
SQL> update t_orascn set id=2 where rownum=1;

1 row updated

SQL> commit;

Commit complete

–再查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;

ORA_ROWSCN ID
———- ———-
559669 2
559669 2

–查询更详细信息
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0500180055010000 559669 Update 2
02001C0068010000 559036 Insert 2
02001C0068010000 559036 559669 Insert 1

–查询操作时间
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:01:12 2
2011-04-11 00:01:12 2

–查询数据详细操作时间
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;

VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0500180055010000 2011-04-11 00:01:12 Update 2
02001C0068010000 2011-04-10 23:59:03 Insert 2
02001C0068010000 2011-04-10 23:59:03 559669 Insert 1

–结论：ora_rowscn在没有默认情况下，如果数据库块中的任何一条记录发生改变，该块中的所有记录的ora_rowscn中对应的scn值都改变

二、创建表含有rowdependencies测试
–创建测试表t_orascn_b
SQL> create table t_orascn_b(id number) rowdependencies;

Table created

SQL> insert into t_orascn_b values(1);

1 row inserted

SQL> insert into t_orascn_b values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2

SQL> insert into t_orascn_b values(3);

1 row inserted

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2
3

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2
560555 3
–说明一点：没有提交ora_rowscn不改变(update)或者不存在（insert）

SQL> update t_orascn_b set id=10 where id<2; 1 row updated SQL> commit;

Commit complete

SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn_b;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:15:38 10
2011-04-11 00:12:37 2
2011-04-11 00:13:28 3

SQL>
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0800290054010000 560614 Update 10
0500130056010000 560555 Insert 3
0A00090001010000 560532 Insert 2
0A00090001010000 560532 560614 Insert 1

SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;

VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0800290054010000 2011-04-11 00:15:38 Update 10
0500130056010000 2011-04-11 00:13:28 Insert 3
0A00090001010000 2011-04-11 00:12:37 Insert 2
0A00090001010000 2011-04-11 00:12:37 560614 Insert 1
–结论：如果创建表时指定了rowdependencies，则ora_rowscn是以行为单位变化，而不是块