內外網資料的簡單單向同步

由於測試需要，需要把外網資料定時同步到內網中，並且需要內網資料可以進行DML操作，且需要在下次同步時內網變化的資料需要全部丟棄。由於是單向同步，可以用可寫物化檢視來實現這個功能。

下面是主要步驟

1、在內網test使用者下建立DBLINK
create database link WAI.SOUCHANG.COM
connect to SOUCHANG2 identified by password
using 'wai1';

2、在外網souchang2使用者下建立物化檢視日誌
住：由於用的是主鍵物化檢視，且無主鍵的表都是一些臨時表，故需要將這些表排除在複製佇列外

/* 批次生成建立物化檢視日誌

select 'CREATE MATERIALIZED VIEW LOG ON '||table_name||';' from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/

3、在內網test使用者下建立重新整理組
BEGIN
DBMS_REFRESH.MAKE (
name => 'test.wai_refresh',
list => '',
next_date => TRUNC(SYSDATE)+4/24,
interval => 'TRUNC(SYSDATE)+4/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => FALSE,
refresh_after_errors => FALSE);
END;

4、在內網test使用者下建立物化檢視

/*批次建立物化檢視
select 'CREATE MATERIALIZED VIEW '||table_name||
' REFRESH FAST WITH PRIMARY KEY for update AS SELECT * FROM ' CREATE_MATERIALIZED_VIEW
from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/

5、在內網test使用者下將物化檢視新增到重新整理組

/*批次將物化檢視新增到重新整理組

select 'BEGIN DBMS_REFRESH.ADD (name =>''test.wai_refresh'',list => ''test.'||table_name||',lax => TRUE); END;' ADD_REFRESH
from tabs a
where exists
(select 1 from user_constraints b where a.table_name=b.table_name and b.constraint_type='P');
*/
6、在內網test使用者下新增索引（用TOAD匯出來的）

由於物化檢視是可寫的的，故必須要同步內外網的序列，以免出錯

souchang2_indexes.sql

7、新增同步序列job

create or replace procedure p_sync_sequence is
l_drop_seq varchar2(512);
l_create_seq varchar2(512);
l_seq_val number;
l_sql_seqval varchar2(512);

l_procedure_name varchar2(100):='p_sync_sequence';
l_sqlcode number;
l_sqlerrm varchar2(4000);
begin
for c_drop_seq in ( select sequence_name from ) loop
begin
l_drop_seq:='drop sequence '|| c_drop_seq.sequence_name;
execute immediate l_drop_seq;
Exception
When Others Then
null;
end;

begin
l_sql_seqval:='select from dual';
execute immediate l_sql_seqval into l_seq_val;
Exception
When Others Then
null;
end;
begin
l_create_seq:='CREATE SEQUENCE '|| c_drop_seq.sequence_name||' INCREMENT BY 1 START WITH '||l_seq_val||' MAXVALUE 999999999999 MINVALUE 1 nocache';
execute immediate l_create_seq;
Exception
When Others Then
null;
end;
end loop;

exception
when others then
rollback;
l_sqlcode:=sqlcode;
l_sqlerrm:=sqlerrm;
insert into PROCEDURE_ERR_RECORD values(seq_PROCEDURE_ERR_RECORD.Nextval,l_procedure_name,l_sqlcode,l_sqlerrm,sysdate);
commit;
dbms_output.put_line(l_sqlcode||':'||l_sqlerrm);

end p_sync_sequence;

var v_job number;
begin
dbms_job.submit(:v_job,'p_sync_sequence;',trunc(sysdate)+6/1440,'trunc(sysdate)+1+6/24');
end;