oracle dblink特别慢,oracle的dblink

rhel5 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.50)(PORT =

1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

1、私有

grant create database link to qin

create database link linktest connect to scott identified by

tiger using 'rhel5'

create synonym dept for dept@linktest;

2、公共

drop public database link test;

create public database link test connect to

scott identified by tiger using 'rhel5' ;

(using '192.168.3.50/orcl';)

另外附远程拷贝表： test为源库表 qintest为目的库表

SYS @ora11g> copy from

qin/qin@192.168.3.50:1521/orcl to qin/qin@ora11g create qintest

using select * from test;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table QINTEST1 created.

14 rows selected from

qin@192.168.3.50:1521/orcl.

14 rows inserted into

QINTEST.

14 rows committed into

QINTEST at qin@ora11g.

SYS @ora11g> copy from qin/qin@192.168.3.50:1521/orcl to

qin/qin@ora11g replace qintest using select *

from test;

set arraysize 5000 改大后速度会快

另附sql loader导入文本文件

sqlldr userid=epay/epay control=/tmp/test.ctl

log=/tmp/test.log data=/tmp/test.csv rows=10

命令参数：userid -- Oracle 的 username/password[@servicename]

control -- 控制文件，可能包含表的数据

log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log

bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad

data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作

errors -- 允许的错误记录数，可以用他来控制一条记录都不能错

rows -- 多少条记录提交一次，默认为 64skip --

跳过的行数，比如导出的数据文件前面几行是表头或其他描

load data

append into table "T_USER_CTRL" -- 操作类型

-- 1) insert --为缺省方式，在数据装载开始时要求表为空

-- 2) append --在表中追加新记录

-- 3) replace --删除旧记录(用 delete from table

语句)，替换成新装载的记录

-- 4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

fields terminated by ' ' -- 数据分隔符

trailing nullcols

(ID,CUST_NUM,CARD_NO,MER_DAY_AMT,MER_DAY_AMT_DATE DATE

"YYYY-MM-DD HH24")

eg：

create table text1 (id number,name varchar2(20),hh

varchar2(10))

vi load.ctl

load data

infile '/tmp/tt.txt'

into table text1

append

fields terminated by ',' optionally enclosed by '*'

trailing nullcols

(ID,NAME,HH)

[oracle@rhel5 tmp]$ sqlldr qin/qin

control=./load.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Mar 1

02:58:36 2019

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

Commit point reached - logical record count 1

select * from text1