不重建快照的情況下可以修改主表 （二）
2024-07-21 02:33:22
供稿：網(wǎng)友
不知道誰在主表上刪除了一個(gè)字段，搞得snapshot無法快速refresh；還好是on PRebuilt table的，再一看發(fā)現(xiàn)mlog里面有記錄，如何才能保證2邊數(shù)據(jù)一致那，我可不想complete refresh。 解決方法 只在只有一個(gè)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中的紀(jì)錄，注重SNAPTIME$$ 時(shí)間字段。SQL> select * from mlog$_st;USER_ID SNAPTIME$$ D O---------- ------------------- - -CHANGE_VECTOR$$------------------------------------------------------------------------------------------------------------------------------------------------------249 4000-01-01 00:00:00 D O0000 創(chuàng)建一個(gè)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. 創(chuàng)建快照后，mlog中的數(shù)據(jù)自動(dòng)清除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 刪除一個(gè)字段SQL> alter table st set unused column username; 用臨時(shí)表保存mlog中的紀(jì)錄SQL> create table mlog_st as select * from mlog$_st;Table created. 執(zhí)行快速刷新失敗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紀(jì)錄，發(fā)現(xiàn)SNAPTIME$$被修改了SQL> select * from mlog$_st;USER_ID SNAPTIME$$ D O---------- ------------------- - -
CHANGE_VECTOR$$------------------------------------------------------------------------------------------------------------------------------------------------------256 2005-06-04 02:39:39 D O0000 刪除snapshot ，預(yù)備重新建立；但對(duì)應(yīng)的mlog內(nèi)容被自動(dòng)刪除，所以之前要保留mlog中的紀(jì)錄，要不然數(shù)據(jù)不一致就完了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. 發(fā)現(xiàn)不一致SQL> select ( select count(*) from st) st, ( select count(*) from sts) sts from dual;ST STS---------- ----------2 3 fast refresh成功，但是數(shù)據(jù)不一致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紀(jì)錄再copy回來。SQL> insert into mlog$_st select * from mlog_st;1 row created.SQL> commit;Commit complete. 刷新后，數(shù)據(jù)一致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數(shù)據(jù)的保存和時(shí)間字段的設(shè)置，4000-01-01 00:00:00 表示還沒有被刷新過。 假如，這個(gè)mlog被多個(gè)snapshot使用，可能過程更復(fù)雜。
