从远程oracle服务器上同步复制数据到本地备份库 create or replace procedure sync_data_from_dblink_db asbegin declare cu
从远程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;