在执行刷新之前,您可以查询实体化视图日志以查看它存储的更改向量类型。这些将是在刷新过程中需要应用于物化视图的更改向量(假设只有一个物化视图依赖于此物化视图日志)。
例如,如果我创建我的表,物化视图日志和物化视图。
SQL> create table foo( col1 number primary key); Table created. SQL> create materialized view log on foo; Materialized view log created. SQL> ed Wrote file afiedt.buf 1 create materialized view mv_foo 2 refresh fast on demand 3 as 4 select * 5* from foo SQL> / Materialized view created. SQL> insert into foo values( 1 ); 1 row created. SQL> insert into foo values( 2 ); 1 row created. SQL> commit; Commit complete.
现在,我刷新物化视图并验证表和物化视图是否同步
SQL> exec dbms_mview.refresh( 'MV_FOO' ); PL/SQL procedure successfully completed. SQL> select * from user_tab_modifications where table_name = 'MV_FOO'; no rows selected SQL> select * from foo; COL1 ---------- 1 2 SQL> select * from mv_foo; COL1 ---------- 1 2
由于两个对象同步,因此物化视图日志为空(物化视图日志将被命名 MLOG$_<<table name>>
MLOG$_<<table name>>
SQL> select * from mlog$_foo; no rows selected
现在,如果我在表中插入一个新行,我会在物化视图日志中看到一行带有 DMLTYPE$$ 的 I 指示一个 INSERT
DMLTYPE$$
I
INSERT
SQL> insert into foo values( 3 ); 1 row created. SQL> select * from mlog$_foo; COL1 SNAPTIME$ D O ---------- --------- - - CHANGE_VECTOR$$ -------------------------------------------------------------------------------- XID$$ ---------- 3 01-JAN-00 I N FE 2.2519E+15
所以你可以做这样的事情来获得挂起的插入,更新和删除的数量。
SELECT SUM( CASE WHEN dmltype$$ = 'I' THEN 1 ELSE 0 END ) num_pending_inserts, SUM( CASE WHEN dmltype$$ = 'U' THEN 1 ELSE 0 END ) num_pending_updates, SUM( CASE WHEN dmltype$$ = 'D' THEN 1 ELSE 0 END ) num_pending_deletes FROM mlog$_foo
但是,刷新实体化视图日志后,此信息就会消失。
另一方面, USER_TAB_MODIFICATIONS 应跟踪自上次收集统计信息以来对物化视图所做的更改的大致数量,就像它将跟踪表的信息一样。你几乎肯定需要打电话 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 如果要在物化视图刷新之前和之后捕获数据,则强制数据可见。
USER_TAB_MODIFICATIONS
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
SELECT inserts, updates, deletes INTO l_starting_inserts, l_starting_updates, l_starting_deletes FROM user_tab_modifications WHERE table_name = 'MV_FOO'; dbms_mview.refresh( 'MV_FOO' ); dbms_stats.flush_database_monitoring_info; SELECT inserts, updates, deletes INTO l_ending_inserts, l_ending_updates, l_ending_deletes FROM user_tab_modifications WHERE table_name = 'MV_FOO'; l_incremental_inserts := l_ending_inserts - l_starting_inserts; l_incremental_updates := l_ending_updates - l_starting_updates; l_incremental_deletes := l_ending_deletes - l_starting_deletes;