oracle 数据导入导出

1、导出txt

select * into

outfile "d:/dataout.txt"

fields terminated by "|"

lines terminated by "\n"

from export_test



2、导入txt



load data

infile "d:/dataout.txt"

into import_test

fields terminated by "|"

lines terminated by "\n"





3、替换特定字符

select *,replace(adstract,char(10)," ") into

outfile "d:/dataout.txt"

fields terminated by "|"

lines terminated by "\n"

from export_test



1、从将234的表复制到 本机

copy from hps/hps@suda234 to scott/guojianbing@ORCL replace CHINA_PATENT using select * from USR_DATA_INP.CHINA_PATENT;

2、将本机的表复制到71

copy from scott/guojianbing@ORCL to hps/hps@suda71 replace CHINA_PATENTusing select * from CHINA_PATENT



set long 150;
set copycommit 1 
set arraysize 150



1、创建DBLINK

create public database link link234tome connect to hps identified by hps using 'suda234';

2、导入一般表

copy from hps/hps@suda234 to scott/guojianbing@ORCL replace TB_MOBIL_TEXT using select * from TB_MOBIL_TEXT;



3、导入clob表(不能运行成功，不能通过DBLINK执行ddl语句，可以手动建一个空表 执行如下insert语句)

create table TB_MOBIL_TEXT as select * from TB_MOBIL_TEXT@LINK234TOME;



4、

insert into TB_MOBIL_TEXT@link71 select * from TB_MOBIL_TEXT



5、查看已经创建的DBLINK

select * from dba_objects where object_type='DATABASE LINK'



6、删除一个dblink

DROP PUBLIC DATABASE LINK dblinkname