309
技術社區[雲棲]
從遠程Oracle服務器上同步複製數據到本地備份庫
create or replace PROCEDURE "SYNC_DATA_FROM_DBLINK_DB" AS
BEGIN
DECLARE
CURSOR c_TabNames IS
SELECT TNAME FROM TAB;
v_TabName c_TabNames%ROWTYPE;
v_SQL VARCHAR2(500);
v_rowcount NUMBER;
v_rc NUMBER:=0;
v_tab NUMBER :=0;
BEGIN
Dbms_Output.put_line(to_char(SYSDATE,'yy-mm-dd hh24:mi:ss')||' 開始從遠程的TEST2實例導入表.');
FOR v_TabName in c_TabNames LOOP
v_SQL := 'DELETE '||v_TabName.TNAME;
EXECUTE IMMEDIATE v_sql;
v_sql := 'INSERT INTO '|| v_tabname.TNAME||' SELECT * FROM '|| v_tabname.TNAME||'@TEST2';
EXECUTE IMMEDIATE v_sql;
COMMIT;
v_SQL :='SELECT COUNT(*) FROM '|| v_tabname.TNAME;
EXECUTE IMMEDIATE v_sql INTO v_rowcount;
v_tab := v_tab +1;
v_rc := v_rc + v_rowcount;
END LOOP;
Dbms_Output.put_line(to_char(SYSDATE,'yy-mm-dd hh24:mi:ss')||' 導入完成，共導入表'||to_char(v_tab)||'張，總記錄數'||to_char(v_rc)||'條記錄。');
END;
END SYNC_DATA_FROM_DBLINK_DB;
最後更新：2017-04-03 16:49:01