table A,table B 在相同的服务器,ods1
table C 和A,B在不同的服务器,ods2....想从A,B取数据后插入C,按以下代码无法实现!!!有没有什么好的办法?INSERT INTO table C(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
table C 和A,B在不同的服务器,ods2....想从A,B取数据后插入C,按以下代码无法实现!!!有没有什么好的办法?INSERT INTO table C(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
INSERT INTO table c@dblink(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
CREATE DATABASE LINK ods1_ods2
CONNECT TO ods2 IDENTIFIED BY ods2的密码
USING '在ods1服务器中的ods2的sid或者是servicename'
建立成功之后,登陆ods1
执行INSERT INTO table c@ods1_ods2(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
如果要是自动执行还要建一个JOB啊.
as
begin
INSERT INTO table c@ods1_ods2(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
commit;
end;var job number(5);
dbms_job.submit(:job,'test',sysdate,sysdate+1);
-- Drop existing database link
drop database link HR2QZJH.US.ORACLE.COM;
-- Create database link
create database link HR2QZJH.US.ORACLE.COM
connect to HRGLJH identified by QZHRJH
using 'test';接着你可以建同义词,方便A,B表的引用
create or replace synonym A for A@ADS1
B一样然后你就可以使用insert了(如果数据链路不通,首先要保证global_name是false)
2、你在操作dblink相关连的表时,注意要写成tablename@dblink,其它的就可以当是在本地服务器上一样了。
例如dblink名字为ods2_link
然后再使用
INSERT INTO c@ods2_link(
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM table A,table B
WHERE ......................
C1,
C2,
C3)
SELECT DISTINCT
a.a1,
a.a2,
b.b1
FROM A@LINK_ODS1, B@LINK_ODS1
WHERE ......................