不重建快照的情况下可以修改主表 （二）
2024-07-21 02:33:22
供稿：网友
不知道谁在主表上删除了一个字段，搞得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 O0000 创建一个on prebuilt snapshotSQL> 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 O0000 删除一个字段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 pathORA-00904: invalid column nameORA-06512: at "SYS.DBMS_SNAPSHOT", line 617ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654ORA-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 O0000 删除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使用，可能过程更复杂。
