应用ROWDEPENDENCIES行级跟踪,查询…

  在Oracle 10g中的引入了ORA_ROWSCN伪列新特性。基于此种伪列所提供的信息，我们可以方便地找出某个数据块或某一个行最近被修改
的时间戳。它又分为两种模式:一种是基于block这是默认的模式(块级跟踪,非行依赖性(NOROWDEPENDENCIES))；还有一种是基于row上，这种模式只能在建里表时指定ROWDEPENDENCIES(行级跟踪)，不可以通过后期的alter  table语句来将表修改为ROWDEPENDENCIES。
       我们知道，每个Block在头部是记录了该block最近事务的SCN的，所以默认情况下，只需要从block头部直接获取这个值就可以了，不需要其他任何的开销，Oracle就能做到这一点。但是这明显是不精确的，一个block中会有很多行记录，每次事务不可能影响到整个block中所有的行，所以这是一个非常不精准的估算值，同一个block的所有记录的ORA_ROWSCN都会是相同的，基本上没有多大的使用价值。
       如果在建表的时候开启行级跟踪选项，Oracle则可以为每一行记录精确的SCN，那么显然不能再直接从block头部获取。要获得足够的信息，肯定要付出一定的代价，Oracle必须为每一行存储这个实际的SCN。所以这个行级跟踪的选项，只能在建表的时候指定，而不能通过alter table来修改现有的表，否则需要修改每一行记录的物理存储格式，代价是可想而知的。

在10g之前，很多系统要实现增量数据抽取，要么通过解析日志，要么加触发器，要么就在表上加一个时间截字段。ORA_ROWSCN其实就是第三种方式，只是这个字段由Oracle来维护，这样可以避免一些应用绕过时间截去更新其他字段带来的问题。
下面是官方说明：
NOROWDEPENDENCIES | ROWDEPENDENCIES
This clause lets you specify whether table will use row-level dependency tracking.
With this feature, each row in the table has a system change number (SCN) that
represents a time greater than or equal to the commit time of the last transaction that
modified the row. You cannot change this setting after table is created.

ROWDEPENDENCIES Specify ROWDEPENDENCIES if you want to enable row-level
dependency tracking. This setting is useful primarily to allow for parallel propagation
in replication environments. It increases the size of each row by 6 bytes.

NOROWDEPENDENCIES Specify NOROWDEPENDENCIES if you do not want table
to use the row-level dependency tracking feature. This is the default.

      在默认情况下，10g下表会以非行依赖性(NOROWDEPENDENCIES)的属性创建，这意味着我们可观察的ORA_ROWSCN信息是以块级跟踪的，无法分辨同一块内的多行间不同的修改时间。为了达到行级粒度的跟踪我们需要在建表时指定基于行依赖性的ROWDEPENDENCIES字句。举例如下：

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> drop table maclean;
Table dropped.

SQL> create table maclean(t1 int,t2 timestamp) ;
Table created.

SQL> insert into maclean values(1,systimestamp);
1 row created.

SQL> commit;
Commit complete.

SQL> insert into maclean values(2,systimestamp);
1 row created.

SQL> commit;
Commit complete.


SQL> alter session set nls_timestamp_format='hh24:mi:ss';
Session altered.

SQL> col t2 for a35
SQL> col orscn for a35

SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean;

ORSCN                               T2
----------------------------------- -----------------------------------
20:30:11                            20:29:56
20:30:11                            20:30:10



create table maclean_rd(t1 int,t2 timestamp) rowdependencies;
Table created.

SQL> select table_name,dependencies from user_tables where dependencies!='DISABLED';
TABLE_NAME                     DEPENDEN
------------------------------ --------
MACLEAN_RD                     ENABLED




SQL> insert into maclean_rd values(1,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into maclean_rd values(2,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean_rd;

ORSCN                               T2
----------------------------------- -----------------------------------
20:31:26                            20:31:25
20:31:35                            20:31:37



SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from maclean_rd;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   1                                94122
                                   1                                94122





SQL> alter system dump datafile '/s01/10gdb/oradata/CLINICA/datafile/o1_mf_system_6fp8d3f0_.dbf' block 94122;
System altered.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/clinica/udump/clinica_ora_12934.trc

block_row_dump:
tab 0, row 0, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8bd
col  0: [ 2]  c1 02
col  1: [11]  78 6f 01 02 15 20 1a 21 d8 52 68
tab 0, row 1, @0x1f70
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8c4
col  0: [ 2]  c1 03
col  1: [11]  78 6f 01 02 15 20 26 02 ab c2 f8





SQL> alter table maclean move tablespace users ROWDEPENDENCIES;
alter table maclean move tablespace users ROWDEPENDENCIES
                                          *
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations