简介:
Sqluldr2 :专业用于大数据量导出工具之一,效率比普通导出快70%。 ( Sqlldr :专业用于导入的工具之一,请注意两个工具的区别。),在使用时,最好用磁盘写入速度快,网络好,网速快的做。
内部实现 :
#include <stdio.h> #include "sqluldr2.h" void main() { void *h = NULL ; SQLULDR2HandleAlloc(&h); if(h != NULL ) { SQLULDR2HandleSetAttr(h, "USER=SYS" ); SQLULDR2HandleSetAttr(h, "QUERY=SELECT * FROM TAB" ); SQLULDR2HandleExecute(h); SQLULDR2HandleFree(h); } }
工具列表:
针对不同平台用不同的导出工具:
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=用户/密码 @tns QUERY="select /*+ parallel(8) */ *from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime>=to_date('2012-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and dt.starttime<=to_date('2012-06-2700:01:59','yyyy-mm-dd hh24: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