关于表获取表数据变化---表级 scn

公司领导提出要同步一张表，是一个新加业务模块的中心数据表，初期决定是使用ogg，有个同事说使用标的一个特性，能够记录scn号，可以获取到表数据的增量，说实在的，我真是第一次听到这个，上网查了一下资料，也做了次试验。

利用表的rowdependencies

创建测试表；

SQL> create table row_scn(id int,txt varchar2(10)) rowdependencies;


Table created.

插入数据;

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


9 rows created.

SQL> 
SQL> 
SQL> 
SQL> commit;    


Commit complete.



SQL> 
SQL>  SELECT id,dbms_rowid.rowid_relative_fno(ROWID) fno,dbms_rowid.rowid_block_number(ROWID) blockno FROM row_scn;


        ID        FNO    BLOCKNO
---------- ---------- ----------
         1          4         12
         2          4         12
         3          4         12
         4          4         12
         5          4         12
         6          4         12
         7          4         12
         8          4         12
         9          4         12


9 rows selected.


SQL> select current_scn from v$database;


CURRENT_SCN
-----------
     640331


SQL> 
SQL> 
SQL> 
SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         1     640099
         2     640099
         3     640099
         4     640099
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099


9 rows selected.


SQL> select * from row_scn;                  


        ID TXT
---------- ----------
         1 abc
         2 abc
         3 abc
         4 abc
         5 abc
         6 abc
         7 abc
         8 abc
         9 abc


9 rows selected.




SQL> update row_scn set txt='abc' where id=2; --修改


1 row updated.


SQL> 
SQL> 
SQL> commit;


Commit complete.


SQL> 
SQL> 
SQL> 
SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         1     640099
         2     640616
         3     640099
         4     640099
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099


9 rows selected.


SQL> select current_scn from v$database;


CURRENT_SCN
-----------
     640660


SQL> update row_scn set txt='abc' where id=3; 修改不提交对ora_rowscn 的影响


1 row updated.


SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         1     640099
         2     640616
         3
         4     640099
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099


9 rows selected.


SQL> commit;


Commit complete.


SQL> 
SQL> 
SQL> 
SQL> select id,txt,ora_rowscn from row_scn;


        ID TXT        ORA_ROWSCN
---------- ---------- ----------
         1 abc            640099
         2 abc            640616
         3 abc            641301
         4 abc            640099
         5 abc            640099
         6 abc            640099
         7 abc            640099
         8 abc            640099
         9 abc            640099


9 rows selected.


SQL> update row_scn set txt='abc' where id=4;  -----更新


1 row updated.


SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         1     640099
         2     640616
         3     641301
         4
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099


9 rows selected.


SQL> rollback;


Rollback complete.


SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         1     640099
         2     640616
         3     641301
         4     640099
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099


9 rows selected.


SQL> alter table row_scn add constraint pk_row_scn primary key(id); ---添加主键


Table altered.


SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table row_scn_snap as select id,ora_rowscn rscn from row_scn;   创建临时表


Table created.



SQL> 
SQL> delete from row_scn where id in (1,2);  ----删除


2 rows deleted.


SQL> 
SQL> 
SQL> update row_scn set txt='abc' where id=3;  更新


1 row updated.


SQL> 
SQL> insert into row_scn values(100,'abc');  插入


1 row created.


SQL> 
SQL> 
SQL> commit;


Commit complete.


SQL> 
SQL> 
SQL> 
SQL> update row_scn set id=200 where id=4;  更新主键


1 row updated.


SQL> 
SQL> commit;


Commit complete.


SQL> 
SQL> 
SQL> 
SQL> select id,ora_rowscn from row_scn;


        ID ORA_ROWSCN
---------- ----------
         3     641700
       200     641711
         5     640099
         6     640099
         7     640099
         8     640099
         9     640099
       100     641700


8 rows selected.


SQL> create table row_scn_snap2 as select id,ora_rowscn rscn from row_scn; --创建临时表二


Table created.


SQL> 
SQL> 
SQL> 
SQL> select id from row_scn_snap2  --查询新增
  2  minus
  3  select id from row_scn_snap;


        ID
----------
       100
       200


SQL> select id from row_scn_snap  查询删除
  2  minus
  3  select id from row_scn_snap2;


        ID
----------
         1
         2
         4


SQL> select s1.id from row_scn_snap s1,row_scn_snap2 s2 --查询更新
  2  where s1.id=s2.id and s1.rscn<>s2.rscn;


        ID
----------
         3


SQL> select s1.id from row_scn_snap s1,row_scn_snap2 s2
  2   where s1.rscn<>s2.rscn;


        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
         1
         2


        ID
----------
         3
         4
         5
         6
         7
         8
         9
         2
         3
         2
         3


        ID
----------
         2
         3
         2
         3
         2
         3
         1
         2
         3
         4
         5


        ID
----------
         6
         7
         8
         9


37 rows selected.

或者;

SQL> SELECT flag, id1, id2
  2    FROM (SELECT (CASE
  3                   WHEN (s1.id = s2.id AND s1.rscn <> s2.rscn) THEN
  4                    'updated'
  5                   WHEN (s1.rscn IS NOT NULL AND s2.rscn IS NULL) THEN
  6                    'deleted'
  7                   WHEN (s1.rscn IS NULL AND s2.rscn IS NOT NULL) THEN
  8                    'inserted'
  9                   ELSE
 10                    '0'
 11                 END) flag,
 12                 s1.id id1,
 13                 s2.id id2
 14            FROM row_scn_snap s1
 15            FULL OUTER JOIN row_scn_snap2 s2
 16              ON s1.id = s2.id)
 17   WHERE flag <> '0'
 18   ORDER BY flag;


FLAG            ID1        ID2
-------- ---------- ----------
deleted           2
deleted           4
deleted           1
inserted                   200
inserted                   100
updated           3          3


6 rows selecte