sqluldr2linux64.bin的使用
2017-02-08 09:54 DataBases 阅读(5368) 评论(0) 编辑 收藏 举报使用sqluldr2linux64.bin的前提是已经安装了Oracle数据库，sqluldr2linux64.bin和Oracle在同一台主机上使用，使用之前需要赋予可执行权限；
[root@node128 opt]# chown -R oracle.oinstall /opt/sqluldr2linux64.bin
[root@node128 opt]# ldd sqluldr2linux64.bin
ldd: warning: you do not have execution permission for `./sqluldr2linux64.bin'
linux-vdso.so.1 => (0x00007fff3c3ff000)
libclntsh.so => not found
libc.so.6 => /lib64/libc.so.6 (0x0000003446200000)
/lib64/ld-linux-x86-64.so.2 (0x0000003445a00000)
[root@node128 opt]# su - oracle
[oracle@node128 opt]$ ldd sqluldr2linux64.bin
ldd: warning: you do not have execution permission for `./sqluldr2linux64.bin'
linux-vdso.so.1 => (0x00007ffff51ff000)
libclntsh.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so (0x00007fcfd02c1000)
libc.so.6 => /lib64/libc.so.6 (0x0000003446200000)
libnnz11.so => /u01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.so (0x00007fcfcfee7000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003445e00000)
libm.so.6 => /lib64/libm.so.6 (0x0000003446e00000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003446600000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003455200000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007fcfcfce4000)
/lib64/ld-linux-x86-64.so.2 (0x0000003445a00000)
[oracle@node128 opt]$ chmod +x sqluldr2linux64.bin
[oracle@node128 opt]$ ./sqluldr2linux64.bin --help
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
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
[oracle@node130 opt]$ sqlplus scott/123456@192.168.8.145/prod
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 13 20:31:45 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
[oracle@node130 opt]$ ./sqluldr2linux64.bin user=scott/123456@192.168.8.145:1521/prod query="select * from dept;" file=/home/oracle/dept.csv
0 rows exported at 2017-04-13 20:35:58, size 0 MB.
4 rows exported at 2017-04-13 20:35:58, size 0 MB.
output file /home/oracle/dept.csv closed at 4 rows, size 0 MB.
[oracle@node130 ~]$ cd /home/oracle/
[oracle@node130 ~]$ cat dept.csv
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
./sqluldr2linux64.bin user=scott/123456@192.168.8.145:1521/prod field=0x7c query="select * from dept;" file=/home/oracle/dept_0x7c.csv
0 rows exported at 2017-04-13 21:35:40, size 0 MB.
4 rows exported at 2017-04-13 21:35:40, size 0 MB.
output file /home/oracle/dept_0x7c.csv closed at 4 rows, size 0 MB.
[oracle@node130 ~]$ cat dept_0x7c.csv
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
[oracle@node130 opt]$ ./sqluldr2linux64.bin user=scott/123456@192.168.8.145:1521/prod query="select * from emp;" file=/home/oracle/emp.csv
0 rows exported at 2017-04-13 20:42:10, size 0 MB.
14 rows exported at 2017-04-13 20:42:10, size 0 MB.
output file /home/oracle/emp.csv closed at 14 rows, size 0 MB.
[oracle@node130 ~]$ cat emp.csv
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
[oracle@node130 opt]$ ./sqluldr2linux64.bin user=scott/123456@192.168.8.145:1521/prod field=0x7c query="select * from emp;" file=/home/oracle/emp_0x7c.csv
0 rows exported at 2017-04-13 21:39:31, size 0 MB.
14 rows exported at 2017-04-13 21:39:31, size 0 MB.
output file /home/oracle/emp_0x7c.csv closed at 14 rows, size 0 MB.
[oracle@node130 ~]$ cat dept_0x7c.csv
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
[oracle@node130 ~]$ cat emp_0x7c.csv
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1987-04-19 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1987-05-23 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
[oracle@node130 opt]$ cat select.sql
select * from emp;
[oracle@node130 opt]$ ./sqluldr2linux64.bin user=scott/123456@192.168.8.145:1521/prod field=0x7c sql=select.sql file=/home/oracle/emp_0x7c_sql.csv
0 rows exported at 2017-04-13 21:57:10, size 0 MB.
14 rows exported at 2017-04-13 21:57:10, size 0 MB.
output file /home/oracle/emp_0x7c_sql.csv closed at 14 rows, size 0 MB.
[oracle@node130 ~]$ cat emp_0x7c_sql.csv
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1987-04-19 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1987-05-23 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
