基于行跟踪的ROWDEPENDENCIES ORA_ROWSCN信息

在Oracle 10g中的引入了ORA_ROWSCN伪列新特性。基于此种伪列所提供的信息，我们可以方便地找出某个数据块或某一个行最近被修改
的时间戳。在默认情况下，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

/* 可以看到默认情况下创建的数据表使用块级依赖性追踪(Block-level Dependency Tracking)
   故而其返回的ORA_ROWSCN伪列仅能代表某数据块最近被更新的SCN
*/

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

/* 包括字典基表在内所有的表都会默认以NOROWDEPENDENCIES创建*/

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

/* 可以看到在行依赖性跟踪情况下，ORA_ROWSCN反映的时间戳与插入的时间戳间仍会有误差；
   显然这种误差部分源于scn_to_timestamp函数使用的smon_scn_time SCN记录表也仅是粗略记录SCN对应的时间戳。
*/

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

/* 以上通过rowid找到了插入的2行所在的数据块*/

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

/* 可以从block dump中看到每行都多出了dscn信息，这就是基于行追踪的行级ORA_ROWSCN信息的来源。
   注意这里的dscn需要占用6个字节的空间，换而言之启用ROWDEPENDENCIES会为每一行多出6个字节的磁盘开销。
*/

/* 此外行级追踪仅能在创建表(create table)的同时指定，而无法使用ALTER TABLE来修改  */

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

© 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. 有趣的数字记录方式
2. How to Make Use of Oracle's Data Compression with Materialized Views.
3. Wrong Results from a RANGE-LIST PARTITIONED TABLE- Bug 5882821
4. 11g r2中对闪回数据归档的增强
5. SQL*Net break/reset to client等待事件
6. Know about Oracle High Water Mark
7. Script:partition table into rowid extent chunks
8. 11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示
9. Oracle Supplemental 补全日志介绍
10. Diagnosing ORA-14097 On Alter Table Exchange Partition