利用行SCN实现表变化跟踪

最新推荐文章于 2021-04-12 10:36:38 发布

haiross

最新推荐文章于 2021-04-12 10:36:38 发布

阅读量2.4k

点赞数

分类专栏： oracle体系架构 Oracle数据库基本管理 oracle转载博文未看文章标签：行SCN undo机制

Oracle数据库基本管理同时被 3 个专栏收录

337 篇文章 6 订阅

订阅专栏

oracle转载博文未看

321 篇文章 0 订阅

订阅专栏

oracle体系架构

260 篇文章 6 订阅

订阅专栏

在应用程序没有记录变化的情况下，实现增量数据传送比较困难。本文测试如何利用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

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

在应用程序没有记录变化的情况下，实现增量数据传送比较困难。本文测试如何利用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;

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

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

9 rows selected.

--会话1
commit;

SQL> select id,ora_rowscn from row_scn;

9 rows selected.

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

SQL> select id,ora_rowscn from row_scn;

9 rows selected.

rollback;

SQL> select id,ora_rowscn from row_scn;

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;

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

在应用程序没有记录变化的情况下，实现增量数据传送比较困难。本文测试如何利用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;

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

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

9 rows selected.

--会话1
commit;

SQL> select id,ora_rowscn from row_scn;

9 rows selected.

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

SQL> select id,ora_rowscn from row_scn;

9 rows selected.

rollback;

SQL> select id,ora_rowscn from row_scn;

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;

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

注释：

对《利用行SCN实现表变化跟踪》的说明：默认时，数据块的数据行格式上是不存在行SCN这个位置的，所以手动开启行SCN功能后，是对之后产生的数据块上的数据行格式产生影响，即有存在行SCN这个位置。而至于在手动开启行SCN功能前的数据块还是不受影响，没有存在行SCN这个位置。

haiross

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
利用行SCN实现表变化跟踪

在应用程序没有记录变化的情况下，实现增量数据传送比较困难。本文测试如何利用ROWDEPENDENCIES获取增量数据。关于ROWDEPENDENCIES属性：创建表时,可以使用ROWDEPENDENCIES属性启用行级依赖跟踪(row-level dependency tracking,在行上记录SCN),该属性有如下特征:*只能在创建表时设置,无法修改;*启用后,每行会增加6字节
复制链接

扫一扫