共计 9660 个字符，预计需要花费 25 分钟才能阅读完成。
本篇内容介绍了“ORACLE 怎么进行导入导出数据”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
简介：
Sqluldr2：专业用于大数据量导出工具之一，效率比普通导出快 70%。( Sqlldr：专业用于导入的工具之一，请注意两个工具的区别。), 在使用时, 最好用磁盘写入速度快，网络好，网速快的做。
工具列表:
针对不同平台用不同的导出工具：
Windows: sqluldr2.exe
Linux(32 位)：sqluldr2_linux32_10204.bin
Linux(64 位): sqluldr2_linux64_10204.bin
使用说明（Windows 平台）：
使用 sqluldr2 的步骤：
1. 打开运行àcmd 进入到 sqluldr2.exe 的当前目录
2. 参数介绍
User= 用户 / 密码 @tns
Query=”查询语句”
File= 导出的路径
Head= 输出信息时，yes 表示要表头，no 表示不要表头
注意：想查看更多参数，请输入 sqluldr2 help=yes
C:\Users\meng\Desktop\sqluldr2 sqluldr2.exe help=yes
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 – 2010, all rights reserved.
License: Free for non-commercial useage, else 100 USD per server.
Usage: SQLULDR2 keyword=value [,keyword=value,…]
Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file
read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
sort = set SORT_AREA_SIZE at session level (UNIT:MB)
hash = set HASH_AREA_SIZE at session level (UNIT:MB)
array = array fetch size
head = print row header(Yes|No)
batch = save to new file for every rows batch (Yes/No)
size = maximum output file piece size (UNIB:MB)
serial = set _serial_direct_read to TRUE at session level
trace = set event 10046 to given level at session level
table = table name in the sqlldr control file
control = sqlldr control file and path.
mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE
buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB)
long = maximum long field size
width = customized max column width (w1:w2:…)
quote = optional quote string
data = disable real data unload (NO, OFF)
alter = alter session SQLs to be execute before unload
safe = use large buffer to avoid ORA-24345 error (Yes|No)
crypt = encrypted user information only (Yes|No)
sedf/t = enable character translation function
null = replace null with given value
escape = escape character for special characters
escf/t = escape from/to characters list
format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.
exec = the command to execute the SQLs.
prehead = column name prefix for head line.
rowpre = row prefix string for each line.
rowsuf = row sufix string for each line.
colsep = separator string between column name and value.
presql = SQL or scripts to be executed before data unload.
postsql = SQL or scripts to be executed after data unload.
lob = extract lob values to single file (FILE).
lobdir = subdirectory count to store lob files .
split = table name for automatically parallelization.
degree = parallelize data copy degree (2-128).
hint = MySQL SQL hint for the Insert, for example IGNORE.
unique = Unique Column List for the MySQL target table.
update = Enable MySQL ON DUPLICATE SQL statement(YES/NO).
for field and record, you can use 0x to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, =0x22 =0x27
C:\Users\meng\Desktop\sqluldr2
代码例子 1：
sqluldr2.exe USER= 用户 / 密码 @tnsQUERY= select /*+ parallel(8) */ *from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime =to_date(2012-06-27 00:00:00 , yyyy-mm-dd hh34:mi:ss) and dt.starttime =to_date(2012-06-2700:01:59 , yyyy-mm-dd hh34:mi:ss) AND dt.msc= devdim105.mapvalue(+) ANDdevdim105.deviceid=15 head=yes FILE=F:\cs_XXX_test.csv
代码例子 2(这种方式用于导出的查询 sql 很长，那么就把 sql 写在 123.sql 文件里)：
sqluldr2.exe USER= 用户 / 密码 @tns sql=123.sqlhead=yes FILE=F:\cs_XXX_test.csv
3. 在 cmd 里，直接把步骤 2 的代码例子 1，贴进去执行。
注：如果执行报报错，就有可能是环境变量 path 的问题，还有就是 plsql 需要的 oci.dll 文件等多个 dll 文件，请放在 sqluldr2 的目录下。
4. 查看结果 F:\cs_XXX_test.csv
5. 打开 csv 里面的内容，就可以用 sqlldr 进行 入库。
– 如果是 linux 版本
[root@localhost export_xdr]# ./sqluldr2_linux64_10204.bin
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 – 2010, all rights reserved.
Usage: SQLULDR2 keyword=value [,keyword=value,…]
Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file
for field and record, you can use 0x to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, =0x22 =0x27
[root@localhost export_xdr]# cd /usr/lib/oracle/11.2/client64/lib/
[root@localhost lib]# ls
glogin.sql libclntsh.so.10.1 libnnz11.so libocci.so.11.1 libocijdbc11.so libsqlplus.so ojdbc6.jar xstreams.jar
libclntsh.so libclntsh.so.11.1 libocci.so libociei.so libsqlplusic.so ojdbc5.jar ottclasses.zip
[root@localhost lib]# ls -l
total 185012
-rw-r–r– 1 root root 368 Sep 17 2011 glogin.sql
lrwxrwxrwx 1 root root 17 Jan 26 17:08 libclntsh.so – libclntsh.so.11.1
lrwxrwxrwx 1 root root 12 Jan 26 17:31 libclntsh.so.10.1 – libclntsh.so
-rw-r–r– 1 root root 52761218 Sep 17 2011 libclntsh.so.11.1
-rw-r–r– 1 root root 7955322 Sep 17 2011 libnnz11.so
lrwxrwxrwx 1 root root 15 Jan 26 17:08 libocci.so – libocci.so.11.1
[root@localhost export_xdr]# history |grep ln
94 find / -name libclntsh.so.10.1
95 find / -name libclntsh.so
116 find / -name libclntsh.so
127 ln libclntsh.so.10.1 libocci.so
128 ln libocci.so libclntsh.so.10.1
130 rm libclntsh.so.10.1 -f
132 ln libclntsh.so libclntsh.so.10.1
134 rm libclntsh.so.10.1 -f
135 ln libclntsh.so.10.1 libclntsh.so
./sqluldr2_linux64_10204.bin user=unxx/ 密码 @192.168.x.x:1521/unxx query= select/*+ parallel(2) */STARTTIME, ENDTIME,phone,imei,rantype,HCITY from v_ps_xxx dt,mv_terminal cc where endtime_par =trunc(sysdate)-1 and endtime_par trunc(sysdate)-1+1/24 and dt.TACID=cc.TACID(+) head=yes field=0x09 text=txt file= /BigData/export_xdr/exportting.txt.tmp
补充：
兄弟们，在安装的时候，是不是经常遇到报错？
比如：
[root@FCJ-2F-21 ~]# ./sqluldr2_linux64_10204.bin
./sqluldr2_linux64_10204.bin: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
[root@FCJ-2F-21 ~]# more /etc/profile
方法一：
这个 so 文件，在安装 oracle 后，肯定是有的，如果没有可以用软连接 ln -s xxxx libclntsh.so.10.1
1. 下面只需配置 LD_LIBRARY_PATH
export ORACLE_HOME=/u01/app/Oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin
[oracle@FCJ-2F-21 lib]$ more /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
pathmunge () {
if ! echo $PATH | /bin/egrep -q (^|:)$1($|:) ; then
if [ $2 = after ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
fi
}
# ksh workaround
if [ -z $EUID -a -x /usr/bin/id ]; then
EUID=`id -u`
UID=`id -ru`
fi
# Path manipulation
if [ $EUID = 0 ]; then
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
fi
# No core files by default
ulimit -S -c 0 /dev/null 2 1
if [ -x /usr/bin/id ]; then
USER= `id -un`
LOGNAME=$USER
MAIL= /var/spool/mail/$USER
fi
HOSTNAME=`/bin/hostname`
HISTSIZE=1000
if [ -z $INPUTRC -a ! -f $HOME/.inputrc ]; then
INPUTRC=/etc/inputrc
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 99 ] [ `id -gn` = `id -un` ]; then
umask 002
else
umask 022
fi
for i in /etc/profile.d/*.sh ; do
if [ -r $i ]; then
if [ ${-#*i} != $- ]; then
. $i
else
. $i /dev/null 2 1
fi
fi
done
unset i
unset pathmunge
export LANG=en_US.UTF-8
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/usr/lib/oracle/11.1/client64/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin
2.source /etc/profile
成功。
方法二：（来自其他网友）
如果共享库文件安装到了 /usr/local/lib(很多开源的共享库都会安装到该目录下)或其它 非 /lib 或 /usr/lib 目录下, 那么在执行 ldconfig 命令前, 还要把新共享库目录加入到共享库配置文件 /etc/ld.so.conf 中, 如下:
# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
# echo /usr/local/lib /etc/ld.so.conf
# ldconfig
—- 实例演示
下载完 sqluldr2，文件夹内容如下：
sqluldr2_linux32_10204.bin 和 sqluldr2_linux64_10204.bin 分别适用于与 linux32 位和 linux64 位操作系统；
sqluldr2.exe 用于 windows 平台。
以下是导出导入过程：
1、首先将 sqluldr2.exe 复制到到 $ORACLE_HOME 的 bin 目录，即可开始使用：
2、查看 help 帮助：
3、执行导出数据命令：
sqluldr2.exe USER=hh/hh@tiod QUERY= select /*+ parallel(2) */ *from hh.ent_person table=ent_person head=yes FILE=C:\ent_person.txt
p.s. head=yes 表示第一行为表头；并且 query 也可以写入一个文件然后使用 sql 选项。
默认分隔字符为逗号，如果列中有特殊字符，可使用 field 选项指定新的分隔字符。
4、数据已经导入到 C:\ent_person.txt，几十万数据秒速。
5、使用 sqlldr 进行导入，首先找到 $ORACLE_HOME 的 bin 目录生成的 ctl 文件，当 sqluldr2 有 table 选项会默认生成 ctl 文件，以用于导入。
6、将数据加载到数据库中：
sqlldr jms/jms@tiod control=ent_person_sqlldr.ctl log=ent_person_sqlldr.log bad=ent_person_sqlldr_bad.log skip=1 errors=5000 rows=5000 bindsize=335542
p.s. skip= 1 表示跳过第一行，从第二行开始导入。
7、最后查看 log 是否有数据没有导入
“ORACLE 怎么进行导入导出数据”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
