分享
 
 
 

用sqlldr从mysql导出一个表的数据到oracle

王朝mysql·作者佚名  2006-04-07
窄屏简体版  字體: |||超大  

用sqlldr从mysql导出一个表的数据到oracle

代码:--------------------------------------------------------------------------------

1 进入mysql

mysql> select UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit

from jb603_jb603_sub3 into outfile 'd:/tmp/603sub.txt';

Query OK, 79537 rows affected (0.48 sec)

mysql> show create table jb603_jb603_sub3;

| jb603_jb603_sub3 | CREATE TABLE `jb603_jb603_sub3` (

`UUID` varchar(32) NOT NULL default '',

`CYC_CODE` char(2) NOT NULL default '',

`b03_05` decimal(11,2) default '0.00',

`b03_06` decimal(11,2) default '0.00',

`b03_07` decimal(11,2) default '0.00',

`b03_08` decimal(11,2) default '0.00',

`b03_09` decimal(9,0) default '0',

`b03_10` decimal(11,2) default '0.00',

`b03_11` decimal(11,2) default '0.00',

`b03_12` decimal(11,2) default '0.00',

`Cate_Item_Code` varchar(7) NOT NULL default '',

`product_name` varchar(60) default '',

`product_unit` varchar(20) default '',

PRIMARY KEY (`UUID`,`CYC_CODE`,`Cate_Item_Code`)

) TYPE=InnoDB |

将语句整理成oracle支持的格式:

CREATE TABLE jb603_jb603_sub3 (

UUID varchar(32) NOT NULL ,

CYC_CODE char(2) NOT NULL ,

b03_05 number(11,2) ,

b03_06 number(11,2) ,

b03_07 number(11,2) ,

b03_08 number(11,2) ,

b03_09 number(9,0) ,

b03_10 number(11,2) ,

b03_11 number(11,2) ,

b03_12 number(11,2) ,

Cate_Item_Code varchar(7) NOT NULL ,

product_name varchar(60) ,

product_unit varchar(20) ,

PRIMARY KEY (UUID,CYC_CODE,Cate_Item_Code));

2 编写sqlldr控制文件t.ctl

load data

infile 'd:/tmp/603sub.txt'

into table jb603_jb603_sub3

replace

fields terminated by x'09'

(UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit)

3运行sqlplus /nolog

SQL> conn lt/lt5@ibmlt

建立jb603_jb603_sub3表

SQL> CREATE TABLE jb603_jb603_sub3 (

2 UUID varchar(32) NOT NULL ,

3 CYC_CODE char(2) NOT NULL ,

4 b03_05 number(11,2) ,

5 b03_06 number(11,2) ,

6 b03_07 number(11,2) ,

7 b03_08 number(11,2) ,

8 b03_09 number(9,0) ,

9 b03_10 number(11,2) ,

10 b03_11 number(11,2) ,

11 b03_12 number(11,2) ,

12 Cate_Item_Code varchar(7) NOT NULL ,

13 product_name varchar(60) ,

14 product_unit varchar(20) ,

15 PRIMARY KEY (UUID,CYC_CODE,Cate_Item_Code));

表已创建。

4 ho进入操作系统命令行

在操作系统命令行运行sqlldr lt/lt_5@ibmlt d:/tmp/t.ctl

....

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

5 exit回到sqlplus

SQL> select count(*)from JB603_JB603_SUB3;

COUNT(*)

----------

79537

6另外,我不明白为什么用外部表方式不能成功

sqlldr lt/lt5@ibmlt d:/tmp/t.ctl external_table=generate_only

产生t.log

SQL*Loader: Release 9.2.0.1.0 - Production on 星期日 3月 26 13:11:41 2006

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

控制文件: d:/tmp/t.ctl

数据文件: d:/tmp/603sub.txt

错误文件: d:/tmp/603sub.bad

废弃文件: 未作指定

:

(可废弃所有记录)

加载数: ALL

跳过数: 0

允许的错误: 50

继续: 未作指定

所用路径: 外部表

表JB603_JB603_SUB3

已加载从每个逻辑记录

插入选项对此表REPLACE生效

列名 位置 长度 中止 包装数据类型

------------------------------ ---------- ----- ---- ---- ---------------------

UUID FIRST * WHT CHARACTER

CYC_CODE NEXT * WHT CHARACTER

B03_05 NEXT * WHT CHARACTER

B03_06 NEXT * WHT CHARACTER

B03_07 NEXT * WHT CHARACTER

B03_08 NEXT * WHT CHARACTER

B03_09 NEXT * WHT CHARACTER

B03_10 NEXT * WHT CHARACTER

B03_11 NEXT * WHT CHARACTER

B03_12 NEXT * WHT CHARACTER

CATE_ITEM_CODE NEXT * WHT CHARACTER

PRODUCT_NAME NEXT * WHT CHARACTER

PRODUCT_UNIT NEXT * WHT CHARACTER

用于外部表的 CREATE TABLE 语句:

------------------------------------------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S"

(

UUID VARCHAR2(32),

CYC_CODE CHAR(2),

B03_05 NUMBER(11,2),

B03_06 NUMBER(11,2),

B03_07 NUMBER(11,2),

B03_08 NUMBER(11,2),

B03_09 NUMBER(9),

B03_10 NUMBER(11,2),

B03_11 NUMBER(11,2),

B03_12 NUMBER(11,2),

CATE_ITEM_CODE VARCHAR2(7),

PRODUCT_NAME VARCHAR2(60),

PRODUCT_UNIT VARCHAR2(20)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY UTL_FILE_DIR

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

BADFILE 'UTL_FILE_DIR':'603sub.bad'

LOGFILE 't.log_xt'

READSIZE 1048576

FIELDS TERMINATED BY 0x'09' LDRTRIM

REJECT ROWS WITH ALL NULL FIELDS

(

UUID CHAR(255)

TERMINATED BY 0x'09',

CYC_CODE CHAR(255)

TERMINATED BY 0x'09',

B03_05 CHAR(255)

TERMINATED BY 0x'09',

B03_06 CHAR(255)

TERMINATED BY 0x'09',

B03_07 CHAR(255)

TERMINATED BY 0x'09',

B03_08 CHAR(255)

TERMINATED BY 0x'09',

B03_09 CHAR(255)

TERMINATED BY 0x'09',

B03_10 CHAR(255)

TERMINATED BY 0x'09',

B03_11 CHAR(255)

TERMINATED BY 0x'09',

B03_12 CHAR(255)

TERMINATED BY 0x'09',

CATE_ITEM_CODE CHAR(255)

TERMINATED BY 0x'09',

PRODUCT_NAME CHAR(255)

TERMINATED BY 0x'09',

PRODUCT_UNIT CHAR(255)

TERMINATED BY 0x'09'

)

)

location

(

'603sub.txt'

)

)REJECT LIMIT UNLIMITED

用于加载内部表的 INSERT 语句:

------------------------------------------------------------------------

INSERT /*+ append */ INTO JB603_JB603_SUB3

(

UUID,

CYC_CODE,

B03_05,

B03_06,

B03_07,

B03_08,

B03_09,

B03_10,

B03_11,

B03_12,

CATE_ITEM_CODE,

PRODUCT_NAME,

PRODUCT_UNIT

)

SELECT

UUID,

CYC_CODE,

B03_05,

B03_06,

B03_07,

B03_08,

B03_09,

B03_10,

B03_11,

B03_12,

CATE_ITEM_CODE,

PRODUCT_NAME,

PRODUCT_UNIT

FROM "SYS_SQLLDR_X_EXT_JB603_JB603_S"

用于清除由以前的语句创建的对象的语句:

------------------------------------------------------------------------

DROP TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S"

从星期日 3月 26 13:11:41 2006开始运行

在星期日 3月 26 13:11:41 2006处运行结束

经过时间为: 00: 00: 00.14

CPU 时间为: 00: 00: 00.04

我摘出建外部表的语句执行

SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S"

2 (

3 UUID VARCHAR2(32),

4 CYC_CODE CHAR(2),

5 B03_05 NUMBER(11,2),

6 B03_06 NUMBER(11,2),

7 B03_07 NUMBER(11,2),

8 B03_08 NUMBER(11,2),

9 B03_09 NUMBER(9),

10 B03_10 NUMBER(11,2),

11 B03_11 NUMBER(11,2),

12 B03_12 NUMBER(11,2),

13 CATE_ITEM_CODE VARCHAR2(7),

14 PRODUCT_NAME VARCHAR2(60),

15 PRODUCT_UNIT VARCHAR2(20)

16 )

17 ORGANIZATION external

18 (

19 TYPE oracle_loader

20 DEFAULT DIRECTORY UTL_FILE_DIR

21 ACCESS PARAMETERS

22 (

23 RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

24 BADFILE 'UTL_FILE_DIR':'603sub.bad'

25 LOGFILE 't.log_xt'

26 READSIZE 1048576

27 FIELDS TERMINATED BY 0x'09' LDRTRIM

28 )

29 location

30 (

31 '603sub.txt'

32 )

33 )

34 /

表已创建。

当603sub.txt记录很少的时候,没问题

SQL> select uuid from SYS_SQLLDR_X_EXT_JB603_JB603_S;

UUID

--------------------------------

00001B3726AD4276AD661393F92F9108

当603sub.txt记录多的时候

SQL> select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S;

select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S

*

ERROR 位于第 1 行:

ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错

ORA-29400: 数据插件错误KUP-04020: found record longer than buffer size

supported, 1048576, in d:tmp603sub.txt

ORA-06512: 在"SYS.ORACLE_LOADER", line 14

ORA-06512: 在line 1

t.log_xt

LOG file opened at 03/26/06 12:52:33

Field Definitions for table SYS_SQLLDR_X_EXT_JB603_JB603_S

Record format DELIMITED BY NEWLINE

Data in file has same endianness as the platform

Rows with all null fields are accepted

Fields in Data Source:

UUID CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

CYC_CODE CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_05 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_06 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_07 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_08 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_09 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_10 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_11 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

B03_12 CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

CATE_ITEM_CODE CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

PRODUCT_NAME CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

PRODUCT_UNIT CHAR (255)

Terminated by "09"

Trim whitespace same as SQL Loader

KUP-04020: found record longer than buffer size supported, 1048576, in d:tmp603sub.txt

KUP-04053: record number 1

-------------------------------------------------------------------------------------------

难道是换行符的原因,但是sqlldr是成功的

果然是换行符的问题

mysql>

select UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit

from jb603_jb603_sub3 into outfile 'd:/tmp/603sub.txt'LINES TERMINATED BY '\r\n';

SQL> select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S;

COUNT(*)

----------

79537

RECORDS DELIMITED BY 0x'0A'

SQL> CREATE TABLE "EXT_S"

2 (

3 UUID VARCHAR2(32),

4 CYC_CODE CHAR(2),

5 B03_05 NUMBER(11,2),

6 B03_06 NUMBER(11,2),

7 B03_07 NUMBER(11,2),

8 B03_08 NUMBER(11,2),

9 B03_09 NUMBER(9),

10 B03_10 NUMBER(11,2),

11 B03_11 NUMBER(11,2),

12 B03_12 NUMBER(11,2),

13 CATE_ITEM_CODE VARCHAR2(7),

14 PRODUCT_NAME VARCHAR2(60),

15 PRODUCT_UNIT VARCHAR2(20)

16 )

17 ORGANIZATION external

18 (

19 TYPE oracle_loader

20 DEFAULT DIRECTORY EXT_DATA_DIR

21 ACCESS PARAMETERS

22 (

23 RECORDS DELIMITED BY 0x'0A' CHARACTERSET ZHS16GBK

24 BADFILE 'UTL_FILE_DIR':'603sub.bad'

25 LOGFILE 't.log_xt'

26 READSIZE 1048576

27 FIELDS TERMINATED BY 0x'09' LDRTRIM

28 )

29 location

30 (

31 '603sub.tx1'

32 )

33 )

34 /

表已创建。

SQL> select count(*) from ext_s;

COUNT(*)

----------

79537

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有