合 Oracle文本导出工具之sqluldr2
Tags： Oracle数据迁移文本导出txt文件csv文件sqluldr2
👉 本文共约838个字，系统预计阅读时间或需4分钟。
简介
oracle数据导出工具sqluldr2可以将数据以csv、txt等格式导出，也可以导出sql格式的数据，适用于大批量数据的导出，导出速度非常快。导出后可以使用oracle loader工具将数据导入。
Oracle的另一个导出工具是spool，导出比较麻烦。
sqluldr2下载地址参考：https://www.jb51.net/database/577354.html
使用
下载完sqluldr解压后，文件夹内容如下：
sqluldr2.exe 用于32位windows平台；
sqluldr2_linux32_10204.bin 适用于linux32位操作系统；
sqluldr2_linux64_10204.bin 适用于linux64位操作系统；
sqluldr264.exe 用于64位windows平台。
复制sqluldr2_linux64_10204.bin到$ORACLE_HOME的bin目录，重命名为sqluldr2
增加环境变量export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib
执行source .bash_profile 应用环境变量。
使用：sqluldr2 --help查看帮助
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | 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) [oracle@db01 ~]$ sqluldr2 help=yes 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). crack = The crack key for the content protection. uncrack = The uncrack key for the content protection. 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 |
query参数如果整表导出，可以直接写表名，如果需要查询运算和where条件，query=“sql文本”，也可以把复杂sql写入到文本中由query调用
导出测试：
本地执行方式：users参数可以省略不写，和expdp username/passwd 方式一样
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
