eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 结束假期 回到北京 | Blog首页 | 姓氏、染色体与传统文化 »

Oracle中如何快速的卸载和加载数据?

前几天有个朋友在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@eygle2

SQL*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 - Production

SQL> 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 - Production

SQL> 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-


历史上的今天...
    >> 2015-03-02文章:
    >> 2011-03-02文章:
    >> 2010-03-02文章:
           ORA-00600 3020 错误案例一则
    >> 2009-03-02文章:
    >> 2008-03-02文章:
    >> 2006-03-02文章:
           搜索引擎 雅虎搜索
    >> 2005-03-02文章:

无觅

By eygle on 2007-03-02 10:20 | Comments (15) | FAQ | 1364 |

15 Comments

早已经有了, 只要在命令行加"table=表名"就可以了

http://www.anysql.net/tools/ociuldr_option_table.html

这一篇是11月份写的.

就是文档没有更新一下, 只是发在blog中, 没有综合起来.

如果用外部表装载进去的话,速度会提升很多,外部表指定nobadfile和nologfile,我们这边测试装载时间由原来的5小时多减少到1小时24分钟.

调sqlldr的参数就达不到这个效果?

加上Parallel+Direct试试?

这个工具楼兄还open source的,相当厚道:)

外部表不容易设置成自动任务处理,如果是例行任务,sqlldr还是具有优势的。

ORACLE遇到的问题一搜全到你的网站上了~~~

AnySQL,

有没有做过大数据量数据卸载Benchmark, 和SQL*Plus and Pro*C 作比较.

0*0a是什么意思?

倒数据时,用小数据量和大数据量测试是不一样的...所以只用小量数据来说明问题有失偏颇...个人认为

ociuldr是经过海量数据测试考验的,数千万级别的应用非常常见。
楼上的有测试结果可以SHow一下看看。

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

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

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

0x0a是assci的换行符,不过我做的实验好像用Sql*loader导不进去。。。。倒是显示正确了记录数目,就是里面没结果。。。。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com