從遠程Oracle伺服器上同步複製資料到本地備份庫

從遠程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;