首页 > 数据库 > Oracle >

oracle数据同步实例

2017-02-06

oracle数据同步实例。创建dblink :create database link db117 --实例。

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;

相关文章
最新文章
热点推荐