DW: Some useful system views when checking Materialized View

最新推荐文章于 2023-11-24 06:00:00 发布

原创最新推荐文章于 2023-11-24 06:00:00 发布 · 913 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#system #table #triggers #oracle #filter #user

Oracle DB 专栏收录该内容

28 篇文章

订阅专栏

本文探讨了Oracle中物化视图(mv)的刷新机制及mv日志的使用方式。介绍了dba_mview_refresh_times视图的作用，mvlog的命名规则及内部触发器的工作原理。同时讲解了创建mvlog时如何指定过滤列，并说明了这些机制在Oracle 11i等版本中的应用。

1. dba_mview_refresh_times/user_mview_refresh_times

This view lists the master table, mv and the last refresh time. When mv are fast refresh mv, the mv log will be purged only when all the mvs are refreshed and those logs are no longer useful.

2. mv log naming format

The materialized view log actually is a table , we can find it in sys.obj$, the name format is MLOG$_<TABLE_NAME> , this name can be found in user_snapshot_logs view. in this table, dmltype is recorded as U, I and D. it also has a snapshot timestamp, I believe Oracle refreshes mv by comparing the last_refresh time with the sanpshot timestamp in this log, it picks rows with snapshot timestamp later then the last refresh time.

3. mv log and internal trigger

oracle captures changes in the table by internal trigger, unforutnately, I'm not able to find the trigger and it's source. The only information I can find is DBA_INTERNAL_TRIGGERS view. according to the view source ,the trigflag in sys.tab$ table reveals the type of such trigger.

4. filter column when create mv log.

create table mytest (a number, b number,c number);

When we create mv log

CREATE MATERIALIZED VIEW LOG ON mytest
PCTFREE 0
PCTUSED 99
WITH ROWID(A,B);

Here we use ROWID(A,B), in the mv log MLOG$_MYTEST, we can see the A, B columns are included. This meaningful columns is useful in system like Oracle 11i in which most tables has no primary key but unique key.