oracle雾化试图_ORACLE11g创建物化视图和清理物化视图⽇
志步骤
清理物化视图⽇志步骤：
以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 fresh('TARGET_MVIEW_1');
PL/SQL procedure successfully completed.
SQL> exec fresh('TARGET_MVIEW_2');
BEGIN fresh('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(*)
----------
发现物化视图⽇志已经清空了。
基表再插⼊⼀条数据：
SQL> /
COUNT(*)
----------
1
SQL> exec fresh('TARGET_MVIEW_1');
PL/SQL procedure successfully completed.
再次查询发现物化视图⽇志，已经⾃动被清除了：
SQL> /
COUNT(*)
----------
再次查询刷新时间：
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 fresh('TARGET_MVIEW_2');
BEGIN fresh('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:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论