频道栏目
IT货架 > > 正文
ORA_ROWSCN
网友分享于:Jan 1, 1970 8:00:00 AM    来源: IT货架   

标签:style   blog   color   数据   ar   div   工作   时间   

这是一个非常重要的特性。从oracle10g开始,oracle在表上引入了一个伪列ORA_ROWSCN。该列记录了每一列最后更改的SCN。但是有两种模式,一种是默认的是data block级别,另一种是row级别,需要在建立表的时候指定ROWDEPENDENCIES,而且不能在表创建后用alter table语句去更改。

我们知道默认情况下SCN存储在data block的头部。这里记载的是该data block的最新更改的SCN。所以默认情况下,你去查一个表的ORA_ROWSCN,同数据块的值是相同的。如下:

SQL> create table test (id number,val char(2000));
SQL> insert into test(id , val) select rownum,object_name from dba_objects where rownum<20;
SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         2                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         3                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         4                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         5                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         6                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         7                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         8                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         9                                60756     618028 08-AUG-14 03.40.58.000000000 PM
        10                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        11                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        12                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        13                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        14                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        15                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        16                                60759     618028 08-AUG-14 03.40.58.000000000 PM
        17                                60759     618028 08-AUG-14 03.40.58.000000000 PM
        18                                60759     618028 08-AUG-14 03.40.58.000000000 PM
        19                                60760     618028 08-AUG-14 03.40.58.000000000 PM

 

上面是准备工作,创建一个表,该表有多个数据块,接下来我们把id=18这一列update看一下结果。

SQL> update test set id=118 where id=18;

1 row updated.

SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         2                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         3                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         4                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         5                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         6                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         7                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         8                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         9                                60756     618028 08-AUG-14 03.40.58.000000000 PM
        10                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        11                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        12                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        13                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        14                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        15                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        16                                60759     618028 08-AUG-14 03.40.58.000000000 PM
        17                                60759     618028 08-AUG-14 03.40.58.000000000 PM
       118                                60759     618028 08-AUG-14 03.40.58.000000000 PM
        19                                60760     618028 08-AUG-14 03.40.58.000000000 PM

19 rows selected.

现在还没有commit。但是按照猜想这60759这个数据块对应的列的SCN都应该变了。不过实际没有变,不知道为什么,需要再研究,不过我们commit一下就会变了。

SQL> commit;

Commit complete.

SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         2                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         3                                60754     618028 08-AUG-14 03.40.58.000000000 PM
         4                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         5                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         6                                60755     618028 08-AUG-14 03.40.58.000000000 PM
         7                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         8                                60756     618028 08-AUG-14 03.40.58.000000000 PM
         9                                60756     618028 08-AUG-14 03.40.58.000000000 PM
        10                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        11                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        12                                60757     618028 08-AUG-14 03.40.58.000000000 PM
        13                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        14                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        15                                60758     618028 08-AUG-14 03.40.58.000000000 PM
        16                                60759     618251 08-AUG-14 03.45.28.000000000 PM
        17                                60759     618251 08-AUG-14 03.45.28.000000000 PM
       118                                60759     618251 08-AUG-14 03.45.28.000000000 PM
        19                                60760     618028 08-AUG-14 03.40.58.000000000 PM

 

 

 

我们再看一下row级别的。

SQL> create table test (id number,val char(2000)) rowdependencies  ;

Table created.

SQL> insert into test(id , val) select rownum,object_name from dba_objects where rownum<20;

19 rows created.

SQL> commit;

Commit complete.

SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         2                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         3                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         4                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         5                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         6                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         7                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         8                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         9                                60756     618618 08-AUG-14 03.57.40.000000000 PM
        10                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        11                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        12                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        13                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        14                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        15                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        16                                60759     618618 08-AUG-14 03.57.40.000000000 PM
        17                                60759     618618 08-AUG-14 03.57.40.000000000 PM
        18                                60759     618618 08-AUG-14 03.57.40.000000000 PM
        19                                60760     618618 08-AUG-14 03.57.40.000000000 PM

19 rows selected.

SQL> update test set id=888 where id=18;

1 row updated.

SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         2                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         3                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         4                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         5                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         6                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         7                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         8                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         9                                60756     618618 08-AUG-14 03.57.40.000000000 PM
        10                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        11                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        12                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        13                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        14                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        15                                60758     618618 08-AUG-14 03.57.40.000000000 PM
ERROR:
ORA-01405: fetched column value is NULL



15 rows selected.

SQL> commit;

Commit complete.

SQL> select id , dbms_rowid.rowid_block_number(ROWID),ora_rowscn,scn_to_timestamp(ora_rowscn) from test;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ------------------------------------ ---------- ---------------------------------------------------------------------------
         1                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         2                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         3                                60754     618618 08-AUG-14 03.57.40.000000000 PM
         4                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         5                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         6                                60755     618618 08-AUG-14 03.57.40.000000000 PM
         7                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         8                                60756     618618 08-AUG-14 03.57.40.000000000 PM
         9                                60756     618618 08-AUG-14 03.57.40.000000000 PM
        10                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        11                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        12                                60757     618618 08-AUG-14 03.57.40.000000000 PM
        13                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        14                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        15                                60758     618618 08-AUG-14 03.57.40.000000000 PM
        16                                60759     618618 08-AUG-14 03.57.40.000000000 PM
        17                                60759     618618 08-AUG-14 03.57.40.000000000 PM
       888                                60759     618643 08-AUG-14 03.58.28.000000000 PM
        19                                60760     618618 08-AUG-14 03.57.40.000000000 PM

19 rows selected.

先创建一个表,指定rowdependencies 然后插入数值。

我们先更新了一列,没commit,然后去select。有意思的是这里出了个错误,很值得研究。

然后我们commit后发现这一列的更改时间知道了。

ORA_ROWSCN,布布扣,bubuko.com

ORA_ROWSCN

标签:style   blog   color   数据   ar   div   工作   时间   

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1