不重建快照的情况下可以修改主表 （二）

　　 

    不知道谁在主表上删除了一个字段，搞得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 suclearcase/" target="_blank" >ccessfully 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使用，可能过程更复杂。

原文转自：http://www.ltesting.net