oracle无法删除物化视图日志,练习物化视图日志异常的处理

有个应用想使用物化视图复制数据，为了应对将来可能遇到的问题，先做些异常测试。

搭建使用rowid 创建物化视图 实现快速更新的环境：

创建用户a, b

grant CONNECT to A;

grant RESOURCE to A;

grant CONNECT to B;

grant RESOURCE to B;

grant DROP ANY MATERIALIZED VIEW to B;

grant CREATE MATERIALIZED VIEW to B;

grant SELECT ANY DICTIONARY to A;

create table a.test1 as select rownum as bh ,t.*

from user_tablespaces t where rownum<2

演示数据：

declare

j number;

i number;

begin

select max(bh) into j from test1;

--  delete from test1 where rownum<1000

--  alter table test1 move

--  update test1 set logging='abcd' where rownum<100

for i in 1 .. 1000 loop

insert into test1

select j + i, t.* from user_tablespaces t where rownum<5;

commit;

end loop;

end;

查看变化：

select '数据量   '||count(0) from test1 union

select '需更新量 '||count(0) from mlog$_test1;

为实现快速更新，创建物化视图日志，授权：

drop MATERIALIZED VIEW LOG on test1;

create  MATERIALIZED VIEW LOG on test1 with rowid;

grant select on a.test1 to b;

grant select on a.mlog$_test1 to b;

创建物化视图：

drop MATERIALIZED VIEW mv_test1;

create materialized view mv_test1

REFRESH fast

WITH rowid

AS SELECT * FROM a.test1;

开始测试

用 truncate table test1后 刷新物化视图时提示：

ORA-12034: "A"."test1" 上的实体化视图日志比上次刷新后的内容新

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: 在 line 2

解决：需要完全刷新，即 exec dbms_mview.refresh('mv_test1','cf');

truncate后 实体化视图日志表mlog$_test1 中的内容全空了

用alter table test1 move; 后 刷新物化视图时也报错：

第 1 行出现错误:

ORA-12034: "A"."test1" 上的实体化视图日志比上次刷新后的内容新

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: 在 line 1

解决：需要完全刷新，应该是因为使用了with rowid选项创建的物化视图。

如果修改了基表结构 (删除字段)报错：

ORA-12008: 实体化视图的刷新路径中存在错误

ORA-00904: "test1"."BUFFER_POOL": 标识符无效

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: 在 line 1

解决：按基表结构改正即可(如果兼容类型，仍可更新成功)

对物化视图 alter table mv_test1 move; 后报错：

ORA-12008: 实体化视图的刷新路径中存在错误

ORA-01502: 索引 'B.I_SNAP$_MV_test1' 或这类索引的分区处于不可用状态

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: 在 line 1

解决：rebuild 这个索引即可

用 exec dbms_mview.explain_mview('mv_test1'); 分析时报错：

ORA-30377: 未找到表 B.MV_CAPABILITIES_TABLE

ORA-00942: 表或视图不存在

ORA-06512: 在 "SYS.DBMS_XRWMV", line 22

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 3008

ORA-06512: 在 line 2

解决：???

一阵乱搞后 alert.log 中发现如下一个job 异常 正好综合使用一遍前面的异常处理方法，有信心。

Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2112.trc:

ORA-12012: 自动执行作业 21 出错

ORA-12031: 不能使用 "A"."T1" 上实体化视图日志中的主键列

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_IREFRESH", line 683

ORA-06512: 在 "SYS.DBMS_REFRESH", line 195

ORA-06512: 在 line 1

解决：

这个job的what是 exec dbms_refresh.refresh('"SYS"."MV_T1_PK"');

对象MV_T1_PK是个物化视图

创建a.t1上的物化视图日志 提示已有 看了一下结构 是用with rowid 选项创建的 删除，

t1也没主键 于是创建主键 用with primary key 重新创建物化视图日志

发现对象MV_T1_PK处于无效状态 重新编译后，手工刷新

SQL> exec dbms_refresh.refresh('"SYS"."MV_T1_PK"');

begin dbms_refresh.refresh('"SYS"."MV_T1_PK"'); end;

ORA-12034: "A"."T1" 上的实体化视图日志比上次刷新后的内容新

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_IREFRESH", line 683

ORA-06512: 在 "SYS.DBMS_REFRESH", line 195

ORA-06512: 在 line 2

这就好办了，执行一次完全更新：

SQL> exec dbms_mview.refresh('MV_T1_PK','c');

PL/SQL procedure successfully completed

SQL>

手工run那个job，不再报错了。