oracle无法删除物化视图日志,物理视图增量刷新无法清除物化视图log

Oracle 10201，OS： Redhat as4

问题描述：创建了一个增量刷新的物化视图，但是每次刷新的时间相当长，

源表数据量变化不大每天40-50w，源表已有数据量大约1500W

创建物化视图log sql

CREATE MATERIALIZED VIEW LOG ON

BANDWIDTH_MONITOR_CHANNEL

WITH rowid(

CHANNEL_ID,

USER_ID,

BUSINESS_TYPE,

NODE_ID,

CUR_TIME,

BANDWIDTH_IN,

BANDWIDTH_OUT

) INCLUDING NEW VALUES;

创建物化视图sql

CREATE MATERIALIZED VIEW MV_BANDWIDTH_MONITOR_USER2

REFRESH FAST ON DEMAND

START WITH TO_DATE('08-01-2008 10:05:17', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/1440

AS

SELECT

a.USER_ID,

a.CUR_TIME,

SUM(a.BANDWIDTH_IN) AS TOTAL_IN,

SUM(a.BANDWIDTH_OUT) AS TOTAL_OUT,

COUNT(a.BANDWIDTH_IN) AS COUNT_IN,

COUNT (a.BANDWIDTH_OUT) AS COUNT_OUT,

COUNT (*) AS COUNT_ALL

FROM

BANDWIDTH_MONITOR_CHANNEL a

GROUP BY

a.USER_ID,

a.CUR_TIME

查看物化视图log中的数据量，发现与源表数据量一致

SQL> select count(*) from mlog$_bandwidth_monitor_ch;

COUNT(*)

----------

15423230

执行一次刷新

SQL> exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"');

PL/SQL procedure successfully completed.

再次检查数据量发现该log中的数量并未减少

SQL> select count(*) from mlog$_bandwidth_monitor_ch;

COUNT(*)

----------

15425623

此时怀疑执行的是完全刷新

查看物化视图的状态，可以得到下一次应该执行哪种刷新

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFR STALENESS

------------------------------ --------- -------------------

MV_BANDWIDTH_MONITOR_USER2     08-JAN-08 NEEDS_COMPILE

再次执行exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"');后

结果仍然是

SQL> select mview_name, last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFR STALENESS

------------------------------ --------- -------------------

MV_BANDWIDTH_MONITOR_USER2     08-JAN-08 NEEDS_COMPILE

由此可以确定是完全刷新，这是什么原因导致的？

检查视图DBA_BASE_TABLE_MVIEWS

该视图表示基于某个表下的物化视图的一些信息。

SQL>  select * from DBA_BASE_TABLE_MVIEWS;

OWNER                          MASTER                         MVIEW_LAS

------------------------------ ------------------------------ ---------

MVIEW_ID

----------

BWMONITOR                      BANDWIDTH_MONITOR_CHANNEL      08-JAN-08

22

BWMONITOR                      BANDWIDTH_MONITOR_CHANNEL      07-DEC-07

1

检查已经注册的物化视图，此时发现MVIEW_ID=1的物化视图没有注册

SQL> select MVIEW_ID from ALL_REGISTERED_MVIEWS;

MVIEW_ID

----------

22

SQL>

而在我们的实际应用中也只有一个物化视图，由此可以判定是MVIEW_ID=1搞的鬼

产生的原因怀疑是第一次创建物化视图的时候没有成功，而Oracle回滚的时候也没有将

视图DBA_BASE_TABLE_MVIEWS中MVIEW_ID=1的记录清除掉

于是将其从mv log中删除

--删除的MVIEW_ID应该是不需要的MVIEW对应的ID

SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(1);

经过漫长等待执行成功

SQL> select * from DBA_BASE_TABLE_MVIEWS;

OWNER                          MASTER                         MVIEW_LAS

------------------------------ ------------------------------ ---------

MVIEW_ID

----------

BWMONITOR                      BANDWIDTH_MONITOR_CHANNEL      08-JAN-08

22

SQL>

检查物化视图日志表数据量明显减少

SQL> select count(*) from mlog$_bandwidth_monitor_ch;

COUNT(*)

----------

262981

执行一次增量刷新

SQL> exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"');

PL/SQL procedure successfully completed.

物化视图日志表中的数据清空

SQL> select count(*) from mlog$_bandwidth_monitor_ch;

COUNT(*)

----------

0

SQL>

清除物化视图注册信息

SQL> exec DBMS_MVIEW.unregister_mview('TEST','ITEM_TAG','SC1.SOUCHANG.COM');

PL/SQL procedure successfully completed

nested exception is java.sql.BatchUpdateException: ORA-12096: error in materialized view log on "BANDWIDTH_MONITOR_CHANNEL"

ORA-00942: table or view does not exist

[oracle@CCN-BJ-6-579 ~]$ oerr ora 12096

12096, 00000, "error in materialized view log on \"%s\".\"%s\""

// *Cause:  There was an error originating from this materialized view log.

//          One possible cause is that schema redefinition has occurred

//          on the master table and one or more columns in the log is now

//          a different type than corresponding master column(s). Another

//          possible cause is that there is a problem accessing the

//          underlying materialized view log table.

// *Action: Check further error messages in stack for more detail about

//          the cause. If there has been schema redefinition, drop the

//          materialized view log and recreate it.

//

经过检查发现没有表BANDWIDTH_MONITOR_CHANNEL的mv log

[oracle@CCN-BJ-6-579 ~]$ sqlplus bwmon/*******

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 9 10:47:42 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> drop materialized view log on BANDWIDTH_MONITOR_CHANNEL;

Materialized view log dropped.

SQL>