oracle实例同步数据,oracle数据同步实例-Oracle

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;

收藏 | 0