使用dblink实现不同oracle数据库之间CLOB/BLOB类型字段数据导入
使用sysdba用户授予用户创建dblink权限:
grant create public database link,create database link to event;
创建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)))';
--创建临时表,处理CLOB/BLOB类型字段
create global temporary table foo
( id integer,
name varchar(256),
a CLOB,
b CLOB )
on commit delete rows;--提交操作,删除所有记录
--读取远程table_name表插入临时表
insert into foo select id,name,a,b
from table_name@orcl_work ;
--读取临时表插入本地table_name表中
insert into table_name (id,name,a,b)
select id,name,a,b from foo;
commit;