ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用行SCN实现表变化跟踪

利用行SCN实现表变化跟踪

原创 Linux操作系统 作者:redhouser 时间:2011-12-27 17:10:52 0 删除 编辑

在应用程序没有记录变化的情况下,实现增量数据传送比较困难。本文测试如何利用ROWDEPENDENCIES获取增量数据。

关于ROWDEPENDENCIES属性:
创建表时,可以使用ROWDEPENDENCIES属性启用行级依赖跟踪(row-level dependency tracking,在行上记录SCN),该属性有如下特征:
*只能在创建表时设置,无法修改;
*启用后,每行会增加6字节的空间需求

测试:
0,创建测试表
1,dump data block
2,更新时,被更新行ora_rowscn变化
3,事务未提交时,当前会话ora_rowscn为空,其他会话ora_rowscn为原值
4,事务回滚时,ora_rowscn不变化
5,使用ROWDEPENDENCIES获取增量数据

--0,创建测试表
create table row_scn(id int,txt varchar2(10)) rowdependencies;

insert into row_scn
select rownum,'abc' from dual
connect by level<10;
commit;

SQL> SELECT id,
  2         dbms_rowid.rowid_relative_fno(ROWID) fno,
  3         dbms_rowid.rowid_block_number(ROWID) blockno
  4    FROM row_scn;
 
        ID   FNO    BLOCKNO
---------- ----- ----------
         1     1      62338
         2     1      62338
         3     1      62338
         4     1      62338
         5     1      62338
         6     1      62338
         7     1      62338
         8     1      62338
         9     1      62338
 
9 rows selected


SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
  319233244
SQL> select id,ora_rowscn from row_scn;
 
        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233211
         3  319233211
         4  319233211
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211
 
9 rows selected

--1,dump
block_row_dump:
tab 0, row 0, @0x1f10
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb  <<---
col  0: [ 2]  c1 02
col  1: [ 3]  61 62 63
tab 0, row 1, @0x1f20
tl: 16 fb: --H-FL-- lb: 0x2  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 03
col  1: [ 3]  61 62 63
tab 0, row 2, @0x1f30
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 04
col  1: [ 3]  61 62 63
tab 0, row 3, @0x1f40
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 05
col  1: [ 3]  61 62 63
tab 0, row 4, @0x1f50
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 06
col  1: [ 3]  61 62 63
tab 0, row 5, @0x1f60
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 07
col  1: [ 3]  61 62 63
tab 0, row 6, @0x1f70
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 08
col  1: [ 3]  61 62 63
tab 0, row 7, @0x1f80
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 09
col  1: [ 3]  61 62 63
tab 0, row 8, @0x1f90
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.13071cbb
col  0: [ 2]  c1 0a
col  1: [ 3]  61 62 63
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 62338 maxblk 62338

--2,更新会记录scn
update row_scn set txt='abc' where id=2;
commit;

 
SQL> select id,ora_rowscn from row_scn;
 
        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271  <<--
         3  319233211
         4  319233211
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211
 
9 rows selected

--3,未提交事务
--会话1
update row_scn set txt='abc' where id=3;
SQL>  select id,ora_rowscn from row_scn;

        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271
         3              <<---当前会话null
         4  319233211
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211

9 rows selected.

--会话2
SQL> select id,ora_rowscn from row_scn;
 
        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271
         3  319233211  <<--没有变化
         4  319233211
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211
 
9 rows selected

--会话1
commit;

SQL> select id,ora_rowscn from row_scn;

        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271
         3  319233871  <<--
         4  319233211
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211

9 rows selected.


--4,回滚事务
--会话1
update row_scn set txt='abc' where id=4;

SQL> select id,ora_rowscn from row_scn;

        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271
         3  319233871
         4
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211

9 rows selected.

rollback;

SQL> select id,ora_rowscn from row_scn;

        ID ORA_ROWSCN
---------- ----------
         1  319233211
         2  319233271
         3  319233871
         4  319233211  <<--恢复原来scn
         5  319233211
         6  319233211
         7  319233211
         8  319233211
         9  319233211

9 rows selected.


--5,根据以上测试,可以根据行scn变化获取增量数据
--假设被跟踪表上有主键
alter table row_scn
  add constraint pk_row_scn primary key(id);


--创建跟踪表
create table row_scn_snap
as
select id,ora_rowscn rscn from row_scn;


--一天的业务变化
delete from row_scn where id in(1,2);

update row_scn set txt='abc' where id=3;

insert into row_scn values(100,'abc');
commit;

--更新主键
update row_scn set id=200 where id=4;
commit;

SQL> select id,ora_rowscn from row_scn;
 
        ID ORA_ROWSCN
---------- ----------
         3  319253768 <<--update
       200  319253770 <<--update primary key
         5  319253661
         6  319253661
         7  319253661
         8  319253661
         9  319253661
       100  319253768 <<--insert
 
8 rows selected

--创建跟踪表
create table row_scn_snap2
as
select id,ora_rowscn rscn from row_scn;

 

--新增
select id from row_scn_snap2
minus
select id from row_scn_snap;
        ID
----------
       100
       200  <<--primary key update
 
--删除
select id from row_scn_snap
minus
select id from row_scn_snap2;
        ID
----------
         1
         2
         4  <<--primary key update


--更新
select s1.id from row_scn_snap s1,row_scn_snap2 s2
where s1.id=s2.id and s1.rscn<>s2.rscn;
 
        ID
----------
         3
 
--使用全外连接查询:
SELECT flag, id1, id2
  FROM (SELECT (CASE
                 WHEN (s1.id = s2.id AND s1.rscn <> s2.rscn) THEN
                  'updated'
                 WHEN (s1.rscn IS NOT NULL AND s2.rscn IS NULL) THEN
                  'deleted'
                 WHEN (s1.rscn IS NULL AND s2.rscn IS NOT NULL) THEN
                  'inserted'
                 ELSE
                  '0'
               END) flag,
               s1.id id1,
               s2.id id2
          FROM row_scn_snap s1
          FULL OUTER JOIN row_scn_snap2 s2
            ON s1.id = s2.id)
 WHERE flag <> '0'
 ORDER BY flag;

FLAG            ID1        ID2
-------- ---------- ----------
deleted           2
deleted           4            <<--primary key update
deleted           1
inserted                   200 <<--primary key update
inserted                   100
updated           3          3
 
6 rows selected


结论,在被跟踪表上有主键的情况下,可以获取增量数据。该方案的不足之处在于,随着被跟踪表的增长,全量比较变化成本较高。
理想情况下,在应用级记录变化可以方便应对海量数据,触发器也可以实现类似逻辑,但会带来维护的复杂性。

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    790625