分类：
2008-10-27 14:26:37
不知道谁在主表上删除了一个字段，搞得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');
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使用，可能过程更复杂。
