« 结束假期 回到北京 | Blog首页 | 姓氏、染色体与传统文化 »
Oracle中如何快速的卸载和加载数据?
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2007/03/dcba_sqlldr_ociuldr.html
前几天有个朋友在MSN上问，如何能更快速的从数据库中卸载和加载数据，他原来是用sql查询spool出来，效率很低。
这让我想起DCBA的一个工具ociuldr，这个工具是用C写成的，通过OCI和数据库交互，方便而且效率很高。
通过ociuldr转储的数据，可以很容易用sqlldr加载入数据库，两者结合使用，效果非凡。
我简单测试了一下，转储很简单：
D:\OraDoc\OracleTools\ociuldr>ociuldr user=scott/tiger@eygle2 query="select * from emp" field=# record=0x0a file=emp.txt
0 rows exported at 2007-02-27 15:47:48
14 rows exported at 2007-02-27 15:47:48
output file emp.txt closed at 14 rows.
创建好测试表：
D:\OraDoc\OracleTools\ociuldr>sqlplus scott/tiger@eygle2SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 2月 27 15:53:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> create table empt as select * from emp where 1=0;
表已创建。
编写简单的控制文件，通过sqlldr轻松加载入数据库：
D:\OraDoc\OracleTools\ociuldr>cat a.ctl
LOAD DATA
INFILE 'emp.txt'
INTO TABLE empt
APPEND
FIELDS TERMINATED BY '#' TRAILING NULLCOLS
(
EMPNO ,
ENAME CHAR ,
JOB CHAR ,
MGR ,
HIREDATE DATE ,
SAL ,
COMM ,
DEPTNO
)D:\OraDoc\OracleTools\ociuldr>sqlldr scott/tiger@eygle2 control=a.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 2月 27 16:01:24 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 14
D:\OraDoc\OracleTools\ociuldr>sqlplus scott/tiger@eygle2
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 2月 27 16:01:37 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> set linesize 120
SQL> select * from empt;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择14行。
SQL>
dcba说将在下一版增加自动控制文件生成的功能，那时候这个工具将会更加方便了。
-The End-
By eygle on 2007-03-02 10:20 | Comments (15) | Posted to FAQ | Edit |Pageviews:
| 相关文章 | 随机文章 |
|
|
Windows无法显示隐藏文件夹之问题解决 繁华，不过是一掬细沙 DBA警世录:Truncate之生产与测试环境 光纤通道故障导致数据库崩溃 丽塔·海华丝和肖申克的救赎 |
留言 (15)
早已经有了, 只要在命令行加"table=表名"就可以了
http://www.anysql.net/tools/ociuldr_option_table.html
这一篇是11月份写的.
Posted by: anysql at March 2, 2007 11:37 AM
就是文档没有更新一下, 只是发在blog中, 没有综合起来.
Posted by: anysql at March 2, 2007 11:41 AM
如果用外部表装载进去的话,速度会提升很多,外部表指定nobadfile和nologfile,我们这边测试装载时间由原来的5小时多减少到1小时24分钟.
Posted by: blue_prince at March 2, 2007 12:35 PM
调sqlldr的参数就达不到这个效果?
加上Parallel+Direct试试?
Posted by: anysql at March 2, 2007 12:51 PM
这个工具楼兄还open source的，相当厚道：）
Posted by: adam at March 2, 2007 1:16 PM
外部表不容易设置成自动任务处理，如果是例行任务，sqlldr还是具有优势的。
Posted by: eygle at March 2, 2007 2:22 PM
ORACLE遇到的问题一搜全到你的网站上了~~~
Posted by: 西雨清风 at March 2, 2007 5:35 PM
AnySQL,
有没有做过大数据量数据卸载Benchmark, 和SQL*Plus and Pro*C 作比较.
Posted by: zhu1 (木匠) at March 3, 2007 1:25 AM
0*0a是什么意思？
Posted by: voice1117 at August 9, 2007 10:38 AM
倒数据时,用小数据量和大数据量测试是不一样的...所以只用小量数据来说明问题有失偏颇...个人认为
Posted by: XIELIANG at December 30, 2007 10:12 AM
ociuldr是经过海量数据测试考验的，数千万级别的应用非常常见。
楼上的有测试结果可以SHow一下看看。
Posted by: eygle at December 30, 2007 11:26 AM
0x0a是assci的换行符，不过我做的实验好像用Sql*loader导不进去。。。。倒是显示正确了记录数目，就是里面没结果。。。。
D:\ociuldr>ociuldr.exe user=milo/password query="select * from students" filed=# record=0x0a filenam
e=students.txt
2050 bytes allocated for column ID (1)
1050 bytes allocated for column FIRST_NAME (2)
1050 bytes allocated for column LAST_NAME (3)
1550 bytes allocated for column MAJOR (4)
2050 bytes allocated for column CURRENT_CREDITS (5)
0 rows exported at 2008-05-05 23:36:48
12 rows exported at 2008-05-05 23:36:48
output file uldrdata.txt closed at 12 rows.
D:\ociuldr>type students.bad
10000|Scott|Smith|Computer Science|11
10001|Margaret|Mason|History|4
10002|Joanne|Junebug|Computer Science|8
10003|Manish|Murgatroid|Economics|8
10004|Patrick|Poll|History|4
10005|Timothy|Taller|History|4
10006|Barbara|Blues|Economics|7
10007|David|Dinsmore|Music|4
10008|Ester|Elegant|Nutrition|8
10009|Rose|Riznit|Music|7
10010|Rita|Razmataz|Nutrition|8
10011|Shay|Shariatpanahy|Computer Science|3
D:\ociuldr>type students.txt
10000|Scott|Smith|Computer Science|11
10001|Margaret|Mason|History|4
10002|Joanne|Junebug|Computer Science|8
10003|Manish|Murgatroid|Economics|8
10004|Patrick|Poll|History|4
10005|Timothy|Taller|History|4
10006|Barbara|Blues|Economics|7
10007|David|Dinsmore|Music|4
10008|Ester|Elegant|Nutrition|8
10009|Rose|Riznit|Music|7
10010|Rita|Razmataz|Nutrition|8
10011|Shay|Shariatpanahy|Computer Science|3
D:\ociuldr>sqlplus milo/password
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:37:44 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Session altered.
Session altered.
Session altered.
SQL> create table student_test as select * from students where 1=100;
Table created.
SQL> select *from student_test;
no rows selected
SQL> host notepad a.ctl
SQL> host type a.ctl
LOAD DATA
INFILE 'students.txt'
INTO TABLE student_test
APPEND
FIELDS TERMINATED BY '#' TRAILING NULLCOLS
(
ID,
FIRST_NAME,
LAST_NAME,
MAJOR,
CURRENT_CREDITS
)
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:\ociuldr>sqlldr milo/password control=a.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:01 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数12
D:\ociuldr>sqlplus milo/password
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Session altered.
Session altered.
Session altered.
SQL> select *from student_test;
no rows selected
Posted by: milo at May 5, 2008 11:44 PM
0x0a是assci的换行符，不过我做的实验好像用Sql*loader导不进去。。。。倒是显示正确了记录数目，就是里面没结果。。。。
D:\ociuldr>ociuldr.exe user=milo/password query="select * from students" filed=# record=0x0a filenam
e=students.txt
2050 bytes allocated for column ID (1)
1050 bytes allocated for column FIRST_NAME (2)
1050 bytes allocated for column LAST_NAME (3)
1550 bytes allocated for column MAJOR (4)
2050 bytes allocated for column CURRENT_CREDITS (5)
0 rows exported at 2008-05-05 23:36:48
12 rows exported at 2008-05-05 23:36:48
output file uldrdata.txt closed at 12 rows.
D:\ociuldr>type students.bad
10000|Scott|Smith|Computer Science|11
10001|Margaret|Mason|History|4
10002|Joanne|Junebug|Computer Science|8
10003|Manish|Murgatroid|Economics|8
10004|Patrick|Poll|History|4
10005|Timothy|Taller|History|4
10006|Barbara|Blues|Economics|7
10007|David|Dinsmore|Music|4
10008|Ester|Elegant|Nutrition|8
10009|Rose|Riznit|Music|7
10010|Rita|Razmataz|Nutrition|8
10011|Shay|Shariatpanahy|Computer Science|3
D:\ociuldr>type students.txt
10000|Scott|Smith|Computer Science|11
10001|Margaret|Mason|History|4
10002|Joanne|Junebug|Computer Science|8
10003|Manish|Murgatroid|Economics|8
10004|Patrick|Poll|History|4
10005|Timothy|Taller|History|4
10006|Barbara|Blues|Economics|7
10007|David|Dinsmore|Music|4
10008|Ester|Elegant|Nutrition|8
10009|Rose|Riznit|Music|7
10010|Rita|Razmataz|Nutrition|8
10011|Shay|Shariatpanahy|Computer Science|3
D:\ociuldr>sqlplus milo/password
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:37:44 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Session altered.
Session altered.
Session altered.
SQL> create table student_test as select * from students where 1=100;
Table created.
SQL> select *from student_test;
no rows selected
SQL> host notepad a.ctl
SQL> host type a.ctl
LOAD DATA
INFILE 'students.txt'
INTO TABLE student_test
APPEND
FIELDS TERMINATED BY '#' TRAILING NULLCOLS
(
ID,
FIRST_NAME,
LAST_NAME,
MAJOR,
CURRENT_CREDITS
)
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
D:\ociuldr>sqlldr milo/password control=a.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:01 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数12
D:\ociuldr>sqlplus milo/password
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Session altered.
Session altered.
Session altered.
SQL> select *from student_test;
no rows selected
Posted by: milo at May 5, 2008 11:45 PM
0x0a是assci的换行符，不过我做的实验好像用Sql*loader导不进去。。。。倒是显示正确了记录数目，就是里面没结果。。。。
D:\ociuldr>sqlldr milo/password control=a.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:01 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数12
D:\ociuldr>sqlplus milo/password
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 5月 5 23:41:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Session altered.
Session altered.
Session altered.
SQL> select *from student_test;
no rows selected
Posted by: milo at May 5, 2008 11:47 PM
0x0a是assci的换行符，不过我做的实验好像用Sql*loader导不进去。。。。倒是显示正确了记录数目，就是里面没结果。。。。
Posted by: milo at May 5, 2008 11:48 PM
