清理物化视图日志步骤：
以scott用户下的一张mview_1为基表建立两个物化视图，然后unregister其中一个(EXEC DBMS_MVIEW.UNREGISTER_MVIEW('MING', 'TARGET_MVIEW_1', 'OGG1')
。向基表中出入三条数据，提交后查看日志表：
创建dblink
create database link scott_link_1
connect to scott
identified by oracle
using 'systest';
create database link scott_link_2
connect to scott
identified by oracle
using 'systest';
scott用户创建物化视图日志：
SQL> sho user
USER is "SCOTT"
SQL> desc mview_1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> create materialized view log on mview_1 with primary key;
Materialized view log created.
SQL> select table_name from user_tables where table_name like 'MLOG$%';
TABLE_NAME
------------------------------
MLOG$_MVIEW_1
创建物化视图：
SQL> sho user
USER is "MING"
SQL> create materialized view target_mview_1 on prebuilt table refresh fast on demand with primary key start with sysdate next
2 to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as
3 select * from scott.mview_1@scott_link_1;
Materialized view created.
SQL> create materialized view target_mview_2 build immediate refresh fast on demand with primary key start with sysdate next
2 to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as
3 select * from scott.mview_1@scott_link_2;
Materialized view created.
scott用户向mview_1中插入4条数据，然后查询日志表：
SQL> select count(*) from scott.MLOG$_MVIEW_1;
COUNT(*)
----------
4
存在了4条记录。
物化视图日志存储在"MLOG$_"为开头的表名中。
首先查看有多少物化视图注册到了刷新机制中
SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;
OWNER NAME MVIEW_SITE MVIEW_ID
--------------- ------------------------------ ------------------------------ ----------
SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS SEEDDATA 0
SH CAL_MONTH_SALES_MV OGG1 21
SH FWEEK_PSCAT_SALES_MV OGG1 22
MING TARGET_MVIEW_1 OGG1 87
MING TARGET_MVIEW_2 OGG1 88
现在删掉一条db_link：
SQL> drop database link scott_link_2;
Database link dropped.
查看基表上的物化视图刷新依赖
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS;
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
--------------- ------------------------------ ------------------- ----------
SCOTT MVIEW_1 2018-01-21 04:00:02 87
SCOTT MVIEW_1 2018-01-21 04:06:12 88
增量刷新两个物化视图
SQL> exec dbms_mview.refresh('TARGET_MVIEW_1');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('TARGET_MVIEW_2');
BEGIN dbms_mview.refresh('TARGET_MVIEW_2'); END;
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
这时候TARGET_MVIEW_2已经不能刷新了；
物化视图日志：
SQL> select count(*) from scott.MLOG$_MVIEW_1;
COUNT(*)
----------
4
正常来说，基于一张表的所有物化视图刷新以后日志表会被清空，现在因为有一个物化视图因为db_link删除而导致刷新失败，物化视图日志是不会自动清除的。
这时候的解决思路通常是：
1.删除无法刷新的物化视图
2.删除无法刷新的物化视图的注册信息
这里我采用第二种方法；
SQL> EXEC DBMS_MVIEW.UNREGISTER_MVIEW('MING', 'TARGET_MVIEW_2', 'OGG1');
PL/SQL procedure successfully completed.
SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;
OWNER NAME MVIEW_SITE MVIEW_ID
--------------- ------------------------------ ------------------------------ ----------
SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS SEEDDATA 0
SH CAL_MONTH_SALES_MV OGG1 21
SH FWEEK_PSCAT_SALES_MV OGG1 22
MING TARGET_MVIEW_1 OGG1 87
可以看到MVIEW_ID=88的物化视图已经没有了。
清除日志，注意千万不要写错mview_id，不然后续刷新会报错。
SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(88);
PL/SQL procedure successfully completed.
这是查询日志：
SQL> select count(*) from scott.MLOG$_MVIEW_1;
COUNT(*)
----------
0
发现物化视图日志已经清空了。
基表再插入一条数据：
SQL> /
COUNT(*)
----------
1
SQL> exec dbms_mview.refresh('TARGET_MVIEW_1');
PL/SQL procedure successfully completed.
再次查询发现物化视图日志，已经自动被清除了：
SQL> /
COUNT(*)
----------
0
再次查询刷新时间：
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS;
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
--------------- ------------------------------ ------------------- ----------
SCOTT MVIEW_1 2018-01-21 04:26:20 87
已经没有88那条信息了。
当然了，表还是在的，只是数据不对了。
SQL> select count(*) from TARGET_MVIEW_2;
COUNT(*)
----------
9
这时重建一下删除的db_link，再尝试刷新一下TARGET_MVIEW_2：
SQL> exec dbms_mview.refresh('TARGET_MVIEW_2');
BEGIN dbms_mview.refresh('TARGET_MVIEW_2'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."MVIEW_1" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
还是报错，报错跟SYS.DBMS_SNAPSHOT有关；DBMS_MVIEW是DBMS_SNAPSHOT的同义词，所以应该是清楚了注册信息的原因。
重新注册，以下摘自官方文档：
DBMS_MVIEW.REGISTER_MVIEW (
mviewowner IN VARCHAR2,
mviewname IN VARCHAR2,
mviewsite IN VARCHAR2,
mview_id IN DATE | BINARY_INTEGER,
flag IN BINARY_INTEGER,
qry_txt IN VARCHAR2,
rep_type IN BINARY_INTEGER := DBMS_MVIEW.REG_UNKNOWN);
但是按照官方文档的写法总是报错：
execute DBMS_MVIEW.REGISTER_MVIEW('MING', 'TARGET_MVIEW_2', 'OGG1',88,33, 'select * from scott.mview_1@scott_link_2', 'DBMS_MVIEW.REG_UNKNOWN');
以下摘自MOS：
How to REGISTER and UNREGISTER a Materialized View - Testcase (文档 ID 1393276.1)
SQL> select s.sowner OWNER, s.vname NAME, snapid,
2 decode(bitand(s.flag,1), 0, 'NO', 'YES') CAN_USE_LOG,
3 decode(bitand(s.flag,2), 0, 'NO', 'YES') UPDATABLE,
4 decode(bitand(s.flag,16), 16, 'ROWID',
5 (decode(bitand(s.flag,32), 32, 'PRIMARY KEY',
6 (decode(bitand(s.flag,536870912), 536870912, 'OBJECT ID',
7 'UNKNOWN'))))) REFRESH_METHOD
8 from sys.snap$ s where s.vname='TARGET_MVIEW_2';
OWNER NAME SNAPID CAN UPD REFRESH_METHOD
--------------- ------------------------------ ---------- --- --- ---------------
MING TARGET_MVIEW_2 88 YES NO PRIMARY KEY
So if we have an mview with primary key, and NOT UPDATABLE and FAST refreshable then this will be:
can_use_log yes 1
updatable no 0
primary key yes 32
So we add these together and get 33 so this is the value for the FLAG column.
注册：
execute dbms_MVIEW.register_mview(-
'MING', -
'TARGET_MVIEW_2',-
'OGG1',-
88,-
33,-
'select * from scott.mview_1@scott_link_2');
PL/SQL procedure successfully completed.
再次查询注册信息，发现已经注册回来了，如下：
21:19:38 SQL> col mview_site for a20
21:19:53 SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;
OWNER NAME MVIEW_SITE MVIEW_ID
------------------------------ ------------------------------ -------------------- ----------
SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS SEEDDATA 0
SH CAL_MONTH_SALES_MV OGG1 21
SH FWEEK_PSCAT_SALES_MV OGG1 22
MING TARGET_MVIEW_2 OGG1 88
MING TARGET_MVIEW_1 OGG1 87
5 rows selected.
基表插入几行数据，再次尝试刷新：
21:23:07 SQL> EXEC dbms_mview.REFRESH('TARGET_MVIEW_1')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.37
21:25:34 SQL> EXEC dbms_mview.REFRESH('TARGET_MVIEW_2')
BEGIN dbms_mview.REFRESH('TARGET_MVIEW_2'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."MVIEW_1" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
这个报错也很容易理解：
因为unregister之后，数据量变化过，我们清除来了一次物化视图日志，所以有gap，这里执行一次完全刷新应该就可以了，试一下：
21:29:31 SQL> exec dbms_mview.refresh('TARGET_MVIEW_2','C');
PL/SQL procedure successfully completed.
验证一下：
基表：
21:27:14 SQL> select count(*) from mview_1;
COUNT(*)
----------
17
1 row selected.
物化视图：
21:30:05 SQL> select count(*) from target_mview_1 union all select count(*) from target_mview_2;
COUNT(*)
----------
17
17
2 rows selected.
在查看官方文档的时候发现有两个purge log相关的存储过程，
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mview_id IN BINARY_INTEGER);
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner IN VARCHAR2,
mviewname IN VARCHAR2,
mviewsite IN VARCHAR2);
上面两个是一个。
DBMS_MVIEW.PURGE_LOG (
master IN VARCHAR2,
num IN BINARY_INTEGER := 1,
flag IN VARCHAR2 := 'NOP');
第二个存储过程执行以后，与基表有关的所有的物化视图需要完全刷新；
第一个存储过程只影响指定的物化视图，这个被影响的物化视图也需要完全刷新；
具体的用法不是很清楚，欢迎交流。
扫一扫
11-18
1303
1303
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
