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

« 如何从备份集中恢复归档日志 | Blog首页 | 王小慧作品 »

如何使用sqlldr的WHEN判断加载数据到不同的数据表

今天有朋友问到sqlldr加载数据到不同表的问题,做了一个测试,示例如下:
创建测试表
D:\Orion>sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:53:59 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

19:53:59 SQL> create table test1 (
19:54:14   2  a1 varchar2(10),
19:54:21   3  a2 varchar2(10),
19:54:26   4  a3 varchar2(10));

表已创建。

已用时间:  00: 00: 00.03
19:54:32 SQL> create table test2 (
19:54:35   2  a1 varchar2(10),
19:54:39   3  a2 varchar2(10),
19:54:40   4  a3 varchar2(10));

表已创建。

已用时间:  00: 00: 00.04
19:54:42 SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production中断开


测试数据:
D:\Orion>cat data.txt
01,KunMing,YunNan
02,BeiJing,BeiJing
02,ShenZhe,ShenZhe
02,TianJin,TianJin
D:\Orion>

控制文件:
D:\Orion>cat data.ctl
LOAD DATA
INFILE 'data.txt'
APPEND INTO TABLE test1
WHEN (2) = '1'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS
( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )
INTO TABLE test2
WHEN (2) = '2'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS
( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )


加载数据:
D:\Orion>sqlldr eygle/eygle errors=20000 log=data.log control=data.ctl

SQL*Loader: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:57:36 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

达到提交点,逻辑记录计数3
达到提交点,逻辑记录计数4


检查结果:
D:\Orion>sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:57:38 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

19:57:38 SQL> select * from test1;

A1         A2         A3
---------- ---------- ----------
01         KunMing    YunNan

已用时间:  00: 00: 00.00
19:57:42 SQL> select * from test2;

A1         A2         A3
---------- ---------- ----------
02         BeiJing    BeiJing
02         ShenZhe    ShenZhe
02         TianJin    TianJin

已用时间:  00: 00: 00.00
19:57:45 SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production中断开

如果不选择分区表,可以使用此方式直接加载数据到不同的数据表中。
.
历史上的今天...
    >> 2017-07-03文章:
    >> 2011-07-03文章:
    >> 2008-07-03文章:
           ORA-00704 与 bootstrap 错误
    >> 2007-07-03文章:
    >> 2006-07-03文章:
           招聘Oracle DBA一名
    >> 2004-07-03文章:

无觅

By eygle on 2005-07-03 19:33 | Comments (1) | Backup&Recovery | 328 |

1 Comment

要是数据文件长短不一

01,KunMing,YunNan
02,BeiJisdng,BeiJing
02,ShenZdhe,ShenZhe
02,TianfffJin,TianJin

就不行了!


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