Home » RDBMS Server » Server Utilities » sqlldr issue loading huge files (Oracle 11GR2)
sqlldr issue loading huge files [message #622047] Wed, 20 August 2014 11:31 Go to next message
anilkumar.cy@gmail.com
Messages: 2
Registered: August 2014
Location: United States
Junior Member
Hi - I am trying to load a .txt file into a table using sqlldr through the windows command prompt. The input file size is around 1 GB. Target table is empty. Here is the command and the control file content. The issue i am facing is as soon as i run this command, i am getting an error from windows that sqlldr.exe has stopped running. Any inputs on resolving this issue would be greatly appreciated.

sqlldr userid=uid/pwd@dbname control=kbm_load.ctl

OPTIONS (DIRECT=TRUE, ROWS=10000)
LOAD DATA
INFILE <input_file>
INTO TABLE CX_MKTG_EXTNL_CNSMR_REF
(
FIRST_NAME POSITION(1:15),
MID_NAME POSITION(16:1),
LAST_NAME POSITION(17:15),
SUFFIX POSITION(42:15),
ADDRESS POSITION(57:40),
CITY POSITION(97:13),
STATE POSITION(110:2),
ZIP POSITION(112:5),
ZIP_P4 POSITION(117:4),
CARRIER_ROUTE POSITION(122:4),
DMA_CODE POSITION(131:4),
CBSA_CODE POSITION(135:5),
LATITUDE POSITION(141:9),
LONGITUDE POSITION(150:9),
CNSMR_ID POSITION(239:12),
GENDER POSITION(254:1),
RACE POSITION(255:1),
FAMILY_POSITION_CODE POSITION(256:1),
LENGTH_OF_RESIDENCE POSITION(258:2),
HOME_STATUS POSITION(260:1),
HOME_VALUE POSITION(261:4),
APT POSITION(265:1),
EST_HOUSEHOLD_INCOME POSITION(266:1),
NET_HOUSEHOLD_INCOME POSITION(267:1),
HOUSEHOLD_INCOME_RANK POSITION(268:1),
EST_HOUSEHOLD_INCOME_QTY POSITION(269:1),
MAIL_RESPONSE POSITION(270:1),
MARRIED_FLAG POSITION(272:1),
ADULT_QTY POSITION(273:3),
HOUSEHOLD_IND_QTY POSITION(276:3),
CHILDREN_QTY POSITION(282:3),
HOUSE_BSNS_FLAG POSITION(300:1),
CALC_AGE POSITION(327:2),
DOB_MONTH POSITION(329:2),
CREDIT_CARD_FLAG POSITION(337:1),
RETAIL_CARD_FLAG POSITION(338:1),
CREDIT_RLTN_FLAG POSITION(339:1),
CREDIT_SRETAIL_RLTN_FLAG POSITION(340:1),
CREDIT_LDEPT_RLTN_FLAG POSITION(341:1),
CREDIT_MRETAIL_RLTN_FLAG POSITION(342:1),
CREDIT_HRETAIL_RLTN_FLAG POSITION(343:1),
HOUSEHOLD_COMP_FLAG POSITION(344:1),
GENERS POSITION(345:2),
ETHNICITY POSITION(347:2),
RELIGION POSITION(349:1),
LANG_PREF POSITION(350:2),
HISPANIC_ETHNICITY POSITION(352:2),
ASSIMILATION_CODE POSITION(354:1),
ETHNICITY_GROUP POSITION(355:1),
ETHNICITY_TECH POSITION(356:2),
ETHNICITY_TECH_LANG POSITION(358:2)
)
Re: sqlldr issue loading huge files [message #622057 is a reply to message #622047] Wed, 20 August 2014 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
what results if you remove DIRECT=TRUE?
Re: sqlldr issue loading huge files [message #622058 is a reply to message #622047] Wed, 20 August 2014 11:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Please use code tags to make your post readable. It would hardly need less than a minute to do so.
Re: sqlldr issue loading huge files [message #622059 is a reply to message #622057] Wed, 20 August 2014 12:02 Go to previous messageGo to next message
anilkumar.cy@gmail.com
Messages: 2
Registered: August 2014
Location: United States
Junior Member
Thanks for responding to the question. I am facing the same problem even if i remove DIRECT = TRUE. Do you think it has anything to do with the Client PC Hardware because the file size is huge and am not able to open it up in the client windows pc?
Re: sqlldr issue loading huge files [message #622062 is a reply to message #622059] Wed, 20 August 2014 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
You can test the size by creating a test file which has only a small number of records to see if it process correctly.
Re: sqlldr issue loading huge files [message #622067 is a reply to message #622047] Wed, 20 August 2014 12:53 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
All of your column definitions are incorrect, except the first one. The numbers in parenthesis should be start and end position. Except the first one, the end position is less than the start position).
Previous Topic: Datapump question
Next Topic: Datapump import lower version to Higher version
Goto Forum:
  


Current Time: Tue Sep 22 10:38:17 CDT 2020