一、临时表解决办法
1、创建DBLINK
(1)使用sysdba用户授予用户创建dblink权限:
grant create public database link,create database link to event;
(2)创建dblink:
create database link orcl_work connect to event identified by event
USING
‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))( CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))’;
2、插入数据
(1)创建临时表,处理CLOB/BLOB类型字段
create global temporary table temp_tab
(id number,
name varchar(256),
a CLOB,
b CLOB)
on commit delete rows;--提交操作,删除所有记录(事务级)
(2)读取远程src_tab表插入临时表
insert into temp_tab
select id, name, a, b from src_tab@orcl_work;
(3)读取临时表插入本地tgt_tab表中
insert into tgt_tab
(id, name, a, b)
select id, name, a, b from temp_tab;
commit;
二、远程往本地导办法
其他办法是到远程往本地表导数据
insert into tgt_tab@tgt_dblink
(id, name, a, b)
select id, name, a, b from src_tab;
commit;