最近一个项目需求,需要用到oracle的物化日志表时间戳,之前都用的最多的场景都是数据同步,看一下物化视图日志
如果光是建立一个不需要fast刷新的物化视图,那么是不需要物化视图日志的。
这篇文章就不展开物化视图详细讲解了,主要是解决开发要取时间戳的问题。
物化视图日志命名:mlog$_表名,如果表名超过20位,那么会截断
物化视图日志字段含义:
SNAPTIME$$:用于表示刷新时间。
DMLTYPE$$:用于表示DML操作类型,I表示INSERT,D表示DELETE,U表示UPDATE。
OLD_NEW$$:用于表示这个值是新值还是旧值。N(EW)表示新值,O(LD)表示旧值,U表示UPDATE操作。
CHANGE_VECTOR$$:表示修改矢量,用来表示被修改的是哪个或哪几个字段。
如果WITH后面跟了ROWID,则物化视图日志中会包含:M_ROW$$:用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY,则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID,则物化视图日志中会包含:SYS_NC_OID$:用来记录每个变化对象的对象ID。
如果WITH后面跟了SEQUENCE,则物化视图日子中会包含:SEQUENCE$$:给每个操作一个SEQUENCE号,从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称,则物化视图日志中会包含这些列。
SNAPTIME$$默认值是4000-01-01 00:00:00,比现在任何时间都大的一个值,表示没有被任何物化视图刷新过,而且如果只有一个物化视图的话,这个表的记录在物化视图刷新完就立即清除掉。
物化视图日志可以用来刷新多个物化视图,关键点就是这个SNAPTIME$$字段,
举例:
源机器:
create table student (id int primary key,name varchar(10));
insert into student values(1,'hank');
CREATE MATERIALIZED VIEW LOG ON student;
select * from student;
ID NAME
---------- ----------
1 tutu
2 kevene
3 kevene
4 kevene
目标机器:
create materialized view mv_hank_1 refresh fast on demand as SELECT * FROM student@applink;
create materialized view mv_hank refresh fast on demand as SELECT * FROM student@applink;
select * from mv_hank_1;
ID NAME
---------- ----------
1 tutu
2 kevene
3 kevene
4 kevene
select * from mv_hank;
ID NAME
---------- ----------
1 tutu
2 kevene
3 kevene
4 kevene
在源机器更新数据,并查看物化视图日志表:
SQL> update student set name='apple';
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from student;
ID NAME
---------- ----------
1 apple
2 apple
3 apple
4 apple
SQL> select * from mlog$_student;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - --------------------
1 4000-01-01 00:00:00 U U 04
2 4000-01-01 00:00:00 U U 04
3 4000-01-01 00:00:00 U U 04
4 4000-01-01 00:00:00 U U 04
目标库进行物化视图刷新:
SQL> exec dbms_mview.refresh('mv_hank','f');
PL/SQL procedure successfully completed.
SQL> select * from mv_hank;
ID NAME
---------- ----------
1 apple
2 apple
3 apple
4 apple
再次查看源端的mlog$_student表,可以看到更新为mv_hank物化视图的刷新时间点:
SQL> select * from mlog$_student;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - --------------------
1 2017-10-24 16:51:20 U U 04
2 2017-10-24 16:51:20 U U 04
3 2017-10-24 16:51:20 U U 04
4 2017-10-24 16:51:20 U U 04
目标端mv_hank_1视图刷新:
SQL> exec dbms_mview.refresh('mv_hank_1','f');
源端mlog$_student表记录已清空:
SQL> select * from mlog$_student;
no rows selected
所以如果要想取这个时间戳,必须创建多个物化视图,才可以看到。
这个是针对11GR2版本之前的操作,如果是11GR2只有的,物化视图日志的清理和物化视图的刷新可以分开
也就是说物化视图日志多了清楚的选项
语法:
CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
[ physical_attributes_clause
| TABLESPACE tablespace
| logging_clause
| { CACHE | NOCACHE }
]...
[ parallel_clause ]
[ table_partitioning_clauses ]
[ WITH [ { OBJECT ID
| PRIMARY KEY
| ROWID
| SEQUENCE
| COMMIT SCN
}
[ { , OBJECT ID
| , PRIMARY KEY
| , ROWID
| , SEQUENCE
| , COMMIT SCN
}
]... ]
(column [, column ]...)
[ new_values_clause ]
] [ mv_log_purge_clause ]
;
子句语法:
new_values_clause::=
{ INCLUDING | EXCLUDING } NEW VALUES
mv_log_purge_clause::=
PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ]
| START WITH datetime_expr [ NEXT datetime_expr
| REPEAT INTERVAL interval_expr
]
| [ START WITH datetime_expr ] { NEXT datetime_expr
| REPEAT INTERVAL interval_expr
}
}
如:
CREATE MATERIALIZED VIEW LOG ON student
PURGE START WITH SYSDATE + 5/1440
REPEAT INTERVAL '5' MINUTE;
保留5分钟的物化视图日志记录,这样的话就不需要创建多个物化视图,直接设置这个时间,就可以从日志表读取相关dml语句的操作
参考:
http://blog.csdn.net/tianlesoftware/article/details/7720580
http://blog.itpub.net/4227/viewspace-629542/