ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g中的新伪列 ora_rowscn

10g中的新伪列 ora_rowscn

原创 Linux操作系统 作者:westzq1984 时间:2009-05-26 11:51:12 0 删除 编辑

SQL> CREATE TABLE test AS SELECT * FROM dba_objects;
 
Table created
 
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                       COUNT(*)
---------- -------------------------------------------------------------------------------- ----------
    239826 26-MAY-09 11.42.03.000000000 AM                                                        9967
 
SQL> UPDATE test SET test.owner='new' WHERE ROWNUM < 10;
 
9 rows updated
 
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                       COUNT(*)
---------- -------------------------------------------------------------------------------- ----------
    239826 26-MAY-09 11.42.03.000000000 AM                                                        9967

--SCN在提交前不改变
SQL> commit;
 
Commit complete
 
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                       COUNT(*)
---------- -------------------------------------------------------------------------------- ----------
    239826 26-MAY-09 11.42.03.000000000 AM                                                        9876
    239980 26-MAY-09 11.42.36.000000000 AM                                                          91

--提交后,SCN更新,但是更行的行数大于91
SQL>
SQL> SELECT COUNT(*)
  2   FROM TEST
  3   WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) IN
  4      (SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  5       FROM TEST
  6       WHERE WNER = 'new')
  7  ;
 
  COUNT(*)
----------
        91
--可见,默认模式下,ora_rowscn是块的SCN
SQL>
SQL> drop table test;
 
Table dropped
 
SQL> CREATE TABLE test ROWDEPENDENCIES AS SELECT * FROM dba_objects;
 
Table created
 
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                       COUNT(*)
---------- -------------------------------------------------------------------------------- ----------
    240003 26-MAY-09 11.43.03.000000000 AM                                                        9967
 
SQL> UPDATE test SET test.owner='new' WHERE ROWNUM < 10;
 
9 rows updated
 
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn)
 
ORA-01405: fetched column value is NULL
 
SQL> SELECT ora_rowscn,COUNT(*) from test GROUP BY  ora_rowscn;
 
ORA_ROWSCN   COUNT(*)
---------- ----------
                    9
    240003       9958

--以ROWDEPENDENCIES建立的表,是行跟踪的,数据在跟新后置空SCN,提交后在置上新的SCN

SQL> commit;
 
Commit complete
 
SQL>
SQL> SELECT ora_rowscn,scn_to_timestamp(ora_rowscn),COUNT(*) from test GROUP BY  ora_rowscn,scn_to_timestamp(ora_rowscn);
 
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                       COUNT(*)
---------- -------------------------------------------------------------------------------- ----------
    240105 26-MAY-09 11.43.48.000000000 AM                                                           9
    240003 26-MAY-09 11.43.03.000000000 AM                                                        9958

具体数据块中是如何处理的,可以参考 http://rdc.taobao.com/blog/dba/html/243_about_ora_rowscn.html

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

上一篇: TSM LINUX 实验配置
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    949027