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

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

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

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