创建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;
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;