linux sql*loader 500,SQL Loader Error 510 SQL*Loader-510: Physical record in data file is larger

在尝试使用SQL*Loader将数据加载到Oracle 11gR2数据库表时,遇到问题。能够成功加载100万条记录的测试文件,但当尝试加载实际的7000万条记录文件时,出现错误SQL*Loader-510。尽管文件格式相同,但1700万条记录的文件无法加载,导致加载失败。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

I am attempting to load data into an Oracle database table (Oracle 11gR2) using sql loader. I am able to load a test file of 1 million records that was sent from our lead company using the control file shown below. However, when they sent the actual files (total of 70 million records) and I tried loading the first file of 1.7 million records which is supposed to be in the exact same format as the test file (and appears to be - text file, comma delimited, same fields), the file will not load using that same control file and I receive the error shown below the Control File. I hope you can help.

Control File

OPTIONS (silent = (feedback, errors, discards, header), direct = true, READSIZE = 20971520 )

unrecoverable load data

infile 'M0000396_Group3_Final.txt' "str ''"

APPEND

into table T3_UNIVERSE

fields terminated by ','

OPTIONALLY ENCLOSED BY '"' AND '"'

trailing nullcols

( FILE_CODE CHAR(1),

FIPS_STATE_CODE CHAR(2),

ZIP_CODE CHAR(5),

ZIP_PLUS_4 CHAR(4),

DELIVERY_POINT_CODE CHAR(20),

CARRIER_ROUTE CHAR(20),

SURNAME CHAR(65),

ADDRESS CHAR(150),

CITY CHAR(65),

STATE CHAR(2),

ADDRESS_QUALITY_CODE CHAR(2),

ADDRESS_TYPE CHAR(2),

HH_AGE CHAR(2),

HH_AGE_IND CHAR(2),

P1_TITLECODE CHAR(2),

P1_GENDER CHAR(2),

P1_GIVENNAME CHAR(65),

P1_MIDDLEINITIAL CHAR(2),

P1_MS CHAR(2),

P1_MS_IND CHAR(2),

P1_MEMBERCODE CHAR(2),

P1_BIRTHMONTH CHAR(2),

P1_BIRTHYEAR CHAR(4),

P1_PERSONKEY CHAR(20),

P2_TITLECODE CHAR(2),

P2_GENDER CHAR(2),

P2_GIVENNAME CHAR(65),

P2_MIDDLEINITIAL CHAR(2),

P2_MS CHAR(2),

P2_MS_IND CHAR(2),

P2_MEMBERCODE CHAR(2),

P2_BIRTHMONTH CHAR(2),

P2_BIRTHYEAR CHAR(4),

P2_PERSONKEY CHAR(20),

P3_TITLECODE CHAR(2),

P3_GENDER CHAR(2),

P3_GIVENNAME CHAR(65),

P3_MIDDLEINITIAL CHAR(2),

P3_MS CHAR(2),

P3_MS_IND CHAR(2),

P3_MEMBERCODE CHAR(2),

P3_BIRTHMONTH CHAR(2),

P3_BIRTHYEAR CHAR(4),

P3_PERSONKEY CHAR(20),

P4_TITLECODE CHAR(2),

P4_GENDER CHAR(2),

P4_GIVENNAME CHAR(65),

P4_MIDDLEINITIAL CHAR(2),

P4_MS CHAR(2),

P4_MS_IND CHAR(2),

P4_MEMBERCODE CHAR(2),

P4_BIRTHMONTH CHAR(2),

P4_BIRTHYEAR CHAR(4),

P4_PERSONKEY CHAR(20),

P5_TITLECODE CHAR(2),

P5_GENDER CHAR(2),

P5_GIVENNAME CHAR(65),

P5_MIDDLEINITIAL CHAR(2),

P5_MS CHAR(2),

P5_MS_IND CHAR(2),

P5_MEMBERCODE CHAR(2),

P5_BIRTHMONTH CHAR(2),

P5_BIRTHYEAR CHAR(4),

P5_PERSONKEY CHAR(20),

LATITUDE CHAR(20),

LONGITUDE CHAR(20),

LATLONG_IND CHAR(2),

INCOME CHAR(2),

INCOME_IND CHAR(2),

NARROWBAND_INCOME CHAR(2),

HOUSEHOLD_KEY CHAR(20)

)

ERROR Message:

SQL*Loader-510: Physical record in data file (M0000396_Group1_Final.txt) is longer than the maximum(20000000)

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Table T3_UNIVERSE:

0 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes:20000000

Total logical records skipped: 0

Total logical records read: 0

Total logical records rejected: 0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 0

Total stream buffers loaded by SQL*Loader load thread: 0

Edited by: ti3r on May 31, 2013 4:13 PM

I don't see a way to attach a file or I would add that to the post.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值