mview

有个mview问题，网授了一下，没找到答案，不过在it168看到这个文章，觉得有意思，就试了，不过没像声明的那样，为了保证一致，还是在最后需要完全刷。

原文出处：http://publish.it168.com/2005/1230/20051230001301.shtml

【IT168 服务器学院】不知道谁在主表上删除了一个字段，搞得snapshot无法快速refresh；还好是on prebuilt table的，再一看发现mlog里面有记录，如何才能保证2边数据一致那，我可不想complete refresh。

解决方法 只在只有一个snapshot的前提下，且master table无任何dml语句。

测试如下：

SQL> create table st as select * from dba_users where rownum<5;

Table created.

SQL> alter table st add primary key (user_id);

Table altered.

SQL> create snapshot log on st;

Materialized view log created.

SQL> delete from st where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

观察mlog中的纪录，注意SNAPTIME$$ 时间字段。

SQL> select * from mlog$_st;

USER_ID SNAPTIME$$ D O

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

CHANGE_VECTOR$$

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

249 4000-01-01 00:00:00 D O

0000

创建一个on prebuilt snapshot

SQL> create table sts as select * from st;

SQL> alter table sts add primary key(user_id);

SQL> create snapshot sts on prebuilt table as select * from st;

Materialized view created.

创建快照后，mlog中的数据自动清除

SQL> select * from mlog$_st;

no rows selected

在主表上作修改

SQL> delete from st where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

观察日志

SQL> select * from mlog$_st;

USER_ID SNAPTIME$$ D O

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

CHANGE_VECTOR$$

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

256 4000-01-01 00:00:00 D O

0000

删除一个字段

SQL> alter table st set unused column username;

用临时表保存mlog中的纪录

SQL> create table mlog_st as select * from mlog$_st;

Table created.

执行快速刷新失败

SQL> exec dbms_snapshot.refresh('sts','f');

BEGIN dbms_snapshot.refresh('sts','f'); END;

*

ERROR at line 1:

ORA-12008: error in snapshot refresh path

ORA-00904: invalid column name

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654

ORA-06512: at line 1

察看mlog纪录，发现SNAPTIME$$被修改了

SQL> select * from mlog$_st;

USER_ID SNAPTIME$$ D O

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

CHANGE_VECTOR$$

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

256 2005-06-04 02:39:39 D O

0000

删除snapshot ，准备重新建立；但对应的mlog内容被自动删除，所以之前要保留mlog中的纪录，要不然数据不一致就完了

SQL> drop snapshot sts;

Materialized view dropped.

SQL> select * from mlog$_st;

no rows selected

在on prebuilt table上作修改

SQL> alter table sts set unused column username;

Table altered.

SQL> create snapshot sts on prebuilt table as select * from st;

Materialized view created.

发现不一致

SQL> select ( select count(*) from st) st, ( select count(*) from sts) sts from dual;

ST STS

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

2 3

fast refresh成功，但是数据不一致

SQL> exec dbms_snapshot.refresh('sts','f'); --这里测试，需要c，否则还是不一致！（测试在oracle10.2.0.1 winxp）

PL/SQL procedure successfully completed.

SQL> select ( select count(*) from st) st, ( select count(*) from sts) sts from dual;

ST STS

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

2 3

将mlog纪录再copy回来。

SQL> insert into mlog$_st select * from mlog_st;

1 row created.

SQL> commit;

Commit complete.

刷新后，数据一致

SQL> exec dbms_snapshot.refresh('sts','f');

PL/SQL procedure successfully completed.

SQL> select ( select count(*) from st) st, ( select count(*) from sts) sts from dual;

ST STS

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

2 2

关键在于mlog数据的保存和时间字段的设置，4000-01-01 00:00:00 表示还没有被刷新过。

如果，这个mlog被多个snapshot使用，可能过程更复杂。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25702/viewspace-464263/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25702/viewspace-464263/