利用行SCN實現表變化跟蹤

在應用程式沒有記錄變化的情況下，實現增量資料傳送比較困難。本文測試如何利用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              <         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  <         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 <       200  319253770 <         5  319253661
         6  319253661
         7  319253661
         8  319253661
         9  319253661
       100  319253768 < 
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  < 
--刪除
select id from row_scn_snap
minus
select id from row_scn_snap2;
        ID
----------
         1
         2
         4  <

--更新
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            <deleted           1
inserted                   200 <inserted                   100
updated           3          3
 
6 rows selected

結論，在被跟蹤表上有主鍵的情況下，可以獲取增量資料。該方案的不足之處在於，隨著被跟蹤表的增長，全量比較變化成本較高。
理想情況下，在應用級記錄變化可以方便應對海量資料，觸發器也可以實現類似邏輯，但會帶來維護的複雜性。