oracle数据同步实例

创建dblink ：
create database link db117 --实例
  connect to test_u identified by test_u
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = plmlk)
    )
  )';
  
  --drop database link db117;
  commit;




创建存储过程
CREATE OR REPLACE PROCEDURE SYNC_DATA_FROM_DBLINK_DB AS


BEGIN




  DECLARE


  CURSOR c_TabNames IS




  SELECT TABLE_NAME FROM user_tables;




  v_TabName c_TabNames%ROWTYPE;




  v_SQL VARCHAR2(4000);


  v_rowcount NUMBER;


  v_rc NUMBER:=0;


  v_tab NUMBER :=0;










  BEGIN
    
  --禁用脚本
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;


end;


    FOR v_TabName in c_TabNames LOOP


      v_SQL := 'DELETE '||v_TabName.TABLE_NAME;
Dbms_Output.put_line(v_SQL);
      EXECUTE IMMEDIATE v_sql;




      v_sql := 'INSERT INTO '|| v_tabname.TABLE_NAME||' SELECT * FROM '|| v_tabname.TABLE_NAME||'@db117';
    Dbms_Output.put_line(v_SQL);
    EXECUTE IMMEDIATE v_sql;


     COMMIT;


     v_SQL :='SELECT COUNT(*) FROM '|| v_tabname.TABLE_NAME;


Dbms_Output.put_line(v_SQL);
      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;
--启用脚本
--SET SERVEROUTPUT ON SIZE 10000 --sqlplus用的
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;




END SYNC_DATA_FROM_DBLINK_DB;




创建任务：
declare
 jobupdate number;
 begin
    dbms_job.submit(jobupdate,'sync_data_from_dblink_db;',sysdate,'sysdate+3/1440');
 end;




select * from user_jobs;




--步骤三：运行刚才创建的job
 begin
 dbms_job.run(44);
 end;




--步骤四：查询该job下次执行的时间
select job,next_date,what from dba_jobs where job=44;
 
--步骤五：删除该job
 begin
 dbms_job.remove(44);
 end;