本文共 6235 字，大约阅读时间需要 20 分钟。
因为业务需求，现在需要把 Oracle 中几千万的数据转移到 Mongodb，如果通过 PL/SQL Develop 导出，速度会比较慢，而且也很占用带宽。发现一款软件 sqluldr2 数据导出速度非常快，我们后面演示通过 sqluldr2 数据导出。
把oracle中的数据导入到csv格式，然后在mongodb中使用mongoimport工具导入到mongo数据库中。
官方下载：
官方下载：程序放在 oracle 的家目录，第一次执行的时候会报错，它回去寻找libclntsh.so这个库文件，这个文件没有在库的环境变量里面，我们可以在oracle的安装目录里面找到，然后我们做个软连接就可以了。
ln -s /u01/oracle/11.0.2.4/lib/libclntsh.so /usr/lib64
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).
query参数如果整表导出，可以直接写表名，如果需要查询运算和where条件，query=“sql文本”，也可以把复杂sql写入到文本中由query调用。
默认是逗号分隔符，通过field参数指定分隔符。
sqluldr2 testuser/testuser query=chen.tt1 field=";"
对于大表可以输出到多个文件中，指定行数分割或者按照文件大小分割，例如：
sqluldr2 testuser/testuser@orcl query="select * from test_table2" file=test_table2_%B.txt batch=yes rows=500000
users参数可以省略不写，和expdp username/passwd 方式一样。
export ORACLE_SID=orclsqluldr2 testuser/testuser query="select * from test" file=test_table1.txt
sqluldr2 user=testuser/testuser@orcl query="select * from test" file=test_table1.txt
sqluldr2 user=testuser/testuser@x.x.x.x:1521/orcl query="select * from test" file=test_table1.txt
严格按照要求写语句，等号两边不能有空格。
一切准备就绪之后，切换到oracle用户下面，执行下面命令。
[oracle@cookie ~]$ ./sqluldr2linux64.bin user=gather/gapass@orcl query="dmp_user_center" head=yes file=/home/oracle/dmp.csv 0 rows exported at 2018-10-09 14:40:27, size 0 MB. 1000000 rows exported at 2018-10-09 14:40:36, size 80 MB. 2000000 rows exported at 2018-10-09 14:40:43, size 144 MB. 3000000 rows exported at 2018-10-09 14:40:50, size 212 MB. 4000000 rows exported at 2018-10-09 14:40:57, size 276 MB. 5000000 rows exported at 2018-10-09 14:41:04, size 340 MB. 6000000 rows exported at 2018-10-09 14:41:11, size 404 MB. 7000000 rows exported at 2018-10-09 14:41:18, size 460 MB. 8000000 rows exported at 2018-10-09 14:41:25, size 504 MB. 9000000 rows exported at 2018-10-09 14:41:31, size 548 MB. 9403362 rows exported at 2018-10-09 14:41:34, size 568 MB. output file /home/oracle/dmp.csv closed at 9403362 rows, size 568 MB.
1、我是整表导出，所以query只填写了表名。
2、head=yes 保留了表头。3、可以看到速度很快，一千万的数据一分钟就导出来了，如果是新机器，我相信速度会更加快很多。
[root@mbasic ~]# mongoimport -udmp -p dmp --db dmp --collection dmp_user_center --type csv --headerline --ignoreBlanks --file dmp.csv 2018-10-09T14:49:13.580+0800 connected to: localhost2018-10-09T14:49:16.551+0800 [........................] dmp.dmp_user_center 5.9 MB/568.5 MB (1.0%)2018-10-09T14:49:19.551+0800 [........................] dmp.dmp_user_center 11.7 MB/568.5 MB (2.1%)2018-10-09T14:49:22.551+0800 [........................] dmp.dmp_user_center 17.7 MB/568.5 MB (3.1%)2018-10-09T14:49:25.551+0800 [........................] dmp.dmp_user_center 23.4 MB/568.5 MB (4.1%)2018-10-09T14:49:28.551+0800 [#.......................] dmp.dmp_user_center 29.1 MB/568.5 MB (5.1%)2018-10-09T14:49:31.551+0800 [#.......................] dmp.dmp_user_center 35.0 MB/568.5 MB (6.2%)2018-10-09T14:54:49.551+0800 [#######################.] dmp.dmp_user_center 563.0 MB/568.5 MB (99.0%)2018-10-09T14:54:52.551+0800 [#######################.] dmp.dmp_user_center 567.4 MB/568.5 MB (99.8%)2018-10-09T14:54:53.447+0800 [########################] dmp.dmp_user_center 568.5 MB/568.5 MB (100.0%)2018-10-09T14:54:53.447+0800 imported 9403362 documents
转载于:https://blog.51cto.com/wzlinux/2296250