用Trigger为MView手动增加时间戳
很多人都遇到过这样的问题：源数据库里的表没有时间戳，或者有时间戳的字段但没有严格执行，而历史数据又可能会经常性得被修改。这对于ETL数据抽取来说将是一个很大的问题，特别是对于不能动源库的情况下，很难解决这个问题。最近又因为这个问题，考虑了很久，最后打算在MView同步数据的基础上，通过Trigger来手动增加时间戳。
考虑对MView操作的原因是因为现有的数据同步就是用MView做的，据说用Stream同步数据也可以用类似的办法添加时间戳的，这个相关的问题以后再研究，讲一下MView下的操作。
首先MView有一些天然的限制，因为最初我考虑的是在建MView的时候就添加一个SYSDATE的字段，这样每次同步就会自动将同步时的sysdate也加入到新的物化视图中，但是这个尝试失败了，因为增量(Fast)更新不支持复杂表结构，而Oracle认为新增字段的表都属于复杂表，会报错如下：
ORA-12015: cannot create a fast refresh materialized view from a complex query
然后又希望能够通过物化视图的on commit刷新模式对表进行刷新，这样可以直接查询ora_rowscn来判断某行的更新时间，但是很遗憾，on commit的刷新模式只针对同库下的更新，不支持远程同步，所以没办法只能用Trigger来自己手工增加时间戳。具体的实验步骤如下：
1、在源库中建立表t1，并添加mv log：
SQL> create table t1(a int primary key,b int);
Table created.
SQL> insert into t1 values(1,2);
1 row created.
SQL> insert into t1 values(3,4);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A B
---------- ----------
1 2
3 4
SQL> create materialized view log on t1 with primary key;
Materialized view log created.
2、在新
库上添加源库的DB LINK：
SQL> create database link wxq_db
2 connect to wangxiaoqi identified by wangxiaoqi
3 using '(DESCRIPTION =
4 (ADDRESS_LIST =
5 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.1.127)(PORT = 1521))
6 )
7 (CONNECT_DATA =
8 (SERVICE_NAME = devWXQowb)
9 )
10 )';
Database link created.
SQL> select * from
t1@wxq_db
;
A B
-------------- --------------
1 2
3 4
3、在新库创建物化视图，设置增量刷新
SQL> create materialized view wxq_t1
2 build deferred
3 refresh fast
4 on demand
5 with primary key
6 as
7 select * from
t1@wxq_db
;
Materialized view created.
SQL> select * from wxq_t1;
no rows selected
--第一次必须要全量刷新
SQL> exec dbms_mview.refresh('wxq_t1','complete');
PL/SQL procedure successfully completed.
SQL> select * from wxq_t1;
A B
---------- ----------
1 2
3 4
4、在新库创建关于表T1的Trigger：
SQL> create table t1_log(a int,c_flag varchar2(2),modifydate date);
Table created.
SQL> create or replace trigger t1_tgr after update or insert or delete on wxq_t1
2 referencing old as oldrow new as newrow for each row
3 begin
4 if inserting then
5 insert into t1_log values(:newrow.a,'I',sysdate);
6 end if;
7 if updating then
8 insert into t1_log values(:oldrow.a,'U',sysdate);
9 end if;
10 if deleting then
11 insert into t1_log values(:oldrow.a,'D',sysdate);
12 end if;
13 end;
14 /
Trigger created.
5、来测试一下在源库中进行插入、删除、修改操作
SQL> select * from t1;
A B
---------- ----------
1 2
3 4
SQL> insert into t1 values(10,20);
1 row created.
SQL> update t1 set b=40 where a=3;
1 row updated.
SQL> delete from t1 where a=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t1;
A B
---------- ----------
10 20
3 40
SQL> column change_vector$$ format a20
SQL> select * from mlog$_t1;
A SNAPTIME$$ DM OL CHANGE_VECTOR$$
---------- ---------- -- -- --------------------
10 4000-01-01 I N FE
3 4000-01-01 U U 04
1 4000-01-01 D O 00
--然后在新库中查看时候生效
SQL> select * from t1_log;
no rows selected
SQL> exec dbms_mview.refresh('wxq_t1','fast');
PL/SQL procedure successfully completed.
SQL> select * from t1_log;
A C_ MODIFYDATE
---------- -- ----------
1 D 2009-06-18
10 I 2009-06-18
3 U 2009-06-18
6、创建最终视图，方便实际操作：
SQL> create or replace view t1 as
2 select wxq_t1.a,wxq_t1.b,t1_log.c_flag,t1_log.modifydate
3 from wxq_t1,t1_log
4 where wxq_t1.a = t1_log.a
5 and t1_log.c_flag in ('I','U')
6 union all
7 select t1_log.a,wxq_t1.b,t1_log.c_flag,t1_log.modifydate
8 from wxq_t1,t1_log
9 where t1_log.a = wxq_t1.a(+)
10 and t1_log.c_flag = 'D';
View created.
SQL> select * from t1;
A B C_ MODIFYDATE
---------- ---------- -- ----------
10 20 I 2009-06-18
3 40 U 2009-06-18
1 D 2009-06-18
这样就创建完成了，不但为新记录添加了时间戳，而且也增加了被删除记录的时间(被删除记录只剩下主键和时间字段信息)。不过这个方法有些过于繁琐，只能在对少数表加时间戳的情况下使用，而不能全库操作，而且针对每一个表都需要建一个trigger，效率也是个问题。但是好处是创建之后对于前台查询时完全透明的，注意最终的查询view名称是与源库的表名一致的，这样的封装性对前台操作的感觉非常好。