Home » RDBMS Server » Server Utilities » no change in rows(sqlldr) (SQL*Loader: Release 10.2.0.1.0)
icon5.gif  no change in rows(sqlldr) [message #574224] Tue, 08 January 2013 02:59 Go to next message
nikhilprakash88
Messages: 5
Registered: January 2013
Location: bangalore
Junior Member

Hi,

when i was loading a large file sqlldr is committing after every record, when i used

OPTIONS(bindsize=20000000, readsize=20000000 , rows=200)
LOAD DATA
LENGTH SEMANTICS CHARACTER
APPEND
INTO TABLE TABLE_NAME
TRAILING NULLCOLS
(
..
)
still the same.. no change at all..

please help..
Re: no change in rows(sqlldr) [message #574225 is a reply to message #574224] Tue, 08 January 2013 03:00 Go to previous messageGo to next message
nikhilprakash88
Messages: 5
Registered: January 2013
Location: bangalore
Junior Member

Log says:

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 1 rows, maximum of 20000000 bytes
Continuation: none specified
Path used: Conventional
Re: no change in rows(sqlldr) [message #574241 is a reply to message #574224] Tue, 08 January 2013 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
still the same.. no change at all..


No change compare to what?

Regards
Michel
Re: no change in rows(sqlldr) [message #574244 is a reply to message #574241] Tue, 08 January 2013 03:50 Go to previous messageGo to next message
nikhilprakash88
Messages: 5
Registered: January 2013
Location: bangalore
Junior Member

i mean commit point should be 200 rows but as log says Bind array: 1 rows, maximum of 20000000 bytes it is still 1 row
Re: no change in rows(sqlldr) [message #574247 is a reply to message #574244] Tue, 08 January 2013 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Guess: you have a LONG or a LOB in your table.

Regards
Michel
Re: no change in rows(sqlldr) [message #574251 is a reply to message #574247] Tue, 08 January 2013 04:11 Go to previous messageGo to next message
nikhilprakash88
Messages: 5
Registered: January 2013
Location: bangalore
Junior Member

no i dont have that in my table.. only CHAR and DATE..
Re: no change in rows(sqlldr) [message #574260 is a reply to message #574251] Tue, 08 January 2013 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the complete control file and description of the table.

Regards
Michel
Re: no change in rows(sqlldr) [message #574264 is a reply to message #574260] Tue, 08 January 2013 05:58 Go to previous messageGo to next message
nikhilprakash88
Messages: 5
Registered: January 2013
Location: bangalore
Junior Member

OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,ROWS=30000,BINDSIZE=20970000)
LOAD DATA
LENGTH SEMANTICS CHARACTER
APPEND
INTO TABLE AR_DISE_LN
TRAILING NULLCOLS
(  
  unix_filename   		CONSTANT 'UNIXFILENAME',
  unix_fileid          	CONSTANT 'FILEIDFIC',
  fic_recnum      		recnum,
  USER_ID                   POSITION(1:10),
  TRANSACTION_NUMBER        CHAR(22),
  DOCUMENT_CIE              CHAR(5),
  DOCUMENT_TYPE             CHAR(2),
  DOCUMENT_NUMBER           CHAR(8),
  LINE_NUMBER               CHAR(7),
  ACCOUNTING_CODE           CHAR(29),
  COMPANY                   CHAR(3),
  FARE_CODE                 CHAR(6),
  SERVICE                   CHAR(6),
  SERVICE_CODE              CHAR(1),
  PARENT_SERVICE            CHAR(6),
  DISCOUNT_PLAN             CHAR(6),
  CURRENCY_CODE             CHAR(3),
  AMOUNT_SIGN               CHAR(1),
  AMOUNT                    CHAR(15),
  EXP_NAME                  CHAR(30),
  EXP_REMARK                CHAR(30),
  ADDRESS_NUMBER            CHAR(8),
  GL_DATE                   DATE   "YYYYMMDD",
  BATCH_SYSDATE             DATE   "YYYYMMDD",
  SERVICE_DATE              DATE   "YYYYMMDD",
  SUBLEDGER                 CHAR(8),
  SUBLEDGER_TYPE            CHAR(1),
  BATCH_NUMBER              CHAR(15),
  DUE_DATE                  DATE   "YYYYMMDD",
  PAYMENT_ITEM              CHAR(3),
  TAX_RATE_CODE             CHAR(10),
  GROSS_AMOUNT_SIGN         CHAR(1),
  GROSS_AMOUNT              CHAR(15),
  TAX_AMOUNT_SIGN           CHAR(1),
  TAX_AMOUNT                CHAR(15),
  VAT_AMOUNT_SIGN           CHAR(1),
  VAT_AMOUNT                CHAR(15),
  REMARK               	CHAR(30),
  STATEMENT_NUMBER          CHAR(13),
  PAYMENT_METHOD            CHAR(1),
  RCE_IDENTIFIER            CHAR(8),
  reject_key        		POSITION(*+1) char(61) TERMINATED BY "|",       -- +1 to avoid the |
  reject_comment    		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment2   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment3   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment4   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment5   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment6   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment7   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment8   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment9   		POSITION(*) CHAR(600)  TERMINATED BY "|",
  reject_comment10  		POSITION(*) CHAR(600)  TERMINATED BY "|"
)




[Edit MC: add code tags]

[Updated on: Tue, 08 January 2013 06:16] by Moderator

Report message to a moderator

Re: no change in rows(sqlldr) [message #574268 is a reply to message #574264] Tue, 08 January 2013 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Please post the description of the table (formatted).

Regards
Michel
Re: no change in rows(sqlldr) [message #574269 is a reply to message #574264] Tue, 08 January 2013 06:19 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The value "bindsize/<max length of a row>" is less than the value given by "rows", in this case Oracle takes "bindsize" and ignore "rows".

From documentation ("Database Utilities"):

Quote:
If the maximum bind array size is too small to accommodate the initial number of rows, SQL*Loader uses a smaller number of rows that fits within the maximum.


Regards
Michel
Previous Topic: EXPDP on NFS error
Next Topic: ROWS Command line parameter
Goto Forum:
  


Current Time: Thu Mar 28 08:50:34 CDT 2024