Home » RDBMS Server » Server Utilities » SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production "CORE 11.2.0.3.0 Production" TNS for Linux: Version 11.2.0.3.0 - Productio)
SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623713] Fri, 12 September 2014 15:50 Go to next message
reddy0422
Messages: 10
Registered: June 2014
Junior Member
I am trying to load CSV flat file which has around 3 million records,it inserts around 1.5 rows and gives the following error,Can any one advise me whats the issue.

Record 1508832: Rejected - Error on table <table_name>, column <column1>.
Field in data file exceeds maximum length
Record 1509159: Rejected - Error on table <table_name>, column <column2>.
Field in data file exceeds maximum length
SQL*Loader-510: Physical record in data file (text.csv) is longer than the maximum(20971520)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Following is the control file,i added char(4000) to the columns,but facing the same errors.

OPTIONS(SKIP=1,readsize=20971520 )
LOAD DATA
REPLACE
INTO TABLE <table_name>
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
column1 char(4000) OPTIONALLY ENCLOSED BY '"',
column2 char(4000) OPTIONALLY ENCLOSED BY '"'
)

Can any one suggest what might be the issue.

Thanks,

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623715 is a reply to message #623713] Fri, 12 September 2014 16:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from OS command below

ls -l text.csv

http://docs.oracle.com/database/121/SUTIL/et_params.htm#SUTIL1398

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623862 is a reply to message #623715] Mon, 15 September 2014 10:15 Go to previous messageGo to next message
reddy0422
Messages: 10
Registered: June 2014
Junior Member
$ ls -l text.csv
-rw-r--r-- 1 j_wwya j_wwya 884429707 Jul 20 22:33 text.csv

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623865 is a reply to message #623713] Mon, 15 September 2014 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Can any one suggest what might be the issue.


One record is missing the end of record marker.

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623866 is a reply to message #623865] Mon, 15 September 2014 11:56 Go to previous messageGo to next message
reddy0422
Messages: 10
Registered: June 2014
Junior Member
Can you please say in detail,i am not sure what you said.

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623867 is a reply to message #623866] Mon, 15 September 2014 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I mean either increase readsize or verify that no record has a field longer than that readsize value which may happen if some records (some "lines") miss the end of record.

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623868 is a reply to message #623867] Mon, 15 September 2014 12:20 Go to previous messageGo to next message
reddy0422
Messages: 10
Registered: June 2014
Junior Member
I had the readsize of readsize=20971520 in control file,what can i keep the readsize now.

Report message to a moderator

Re: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520) [message #623869 is a reply to message #623868] Mon, 15 September 2014 12:35 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Yes you have and Oracle said: "Physical record in data file (text.csv) is longer than the maximum(20971520)", so larger than your readsize.

Report message to a moderator

Previous Topic: Parallel IMPDP not working, Jobs spawning only a single Worker Process
Next Topic: Import error ORA-31694
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:49:37 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.