This discussion is archived
7 Replies Latest reply: Jul 13, 2013 10:04 PM by Barbara Boehmer RSS

sqlldr bindsize parameter

652398 Newbie
Currently Being Moderated
Using sqlldr to import data:
sqlldr ${user_id} control=${ctl_file} rows=10000 bindsize=8192 readsize=8192 errors=999999 log=${log_file}/${today_DATE}.log

When executing:
Commit point reached - logical record count 2
Commit point reached - logical record count 4
Commit point reached - logical record count 6
Commit point reached - logical record count 8
Commit point reached - logical record count 10
Commit point reached - logical record count 12
...

In data file, there is only 80 characters(that is 80 bytes) in every row. However, in log file:
Space allocated for bind array: 6714 bytes(2 rows)

Why 6714 bytes is here? Except the 160 bytes for two rows, what's the other sizes?

Thanks a lot for your kindly reply.
  • 1. Re: sqlldr bindsize parameter
    DK2010 Guru
    Currently Being Moderated
    What is the OS/DB version you are using
  • 2. Re: sqlldr bindsize parameter
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    This doesn't explain everything but its a good start point

    http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch05.htm#1005908

    It seems that the bind array is based on the max size of he datatype specified for each column basically - it doesn't take into account what it actually finds in the data.

    You might have a char column specified in your control file but if you don't specify char(1) it allocates 255 bytes which is the max -sqlldr doesn't know that he data is much smaller than that you have to tell it the smallest size it could be.

    Cheers,
    Harry
  • 3. Re: sqlldr bindsize parameter
    652398 Newbie
    Currently Being Moderated
    Oracle 9.2.0.7
  • 4. Re: sqlldr bindsize parameter
    652398 Newbie
    Currently Being Moderated
    Table has 14 fields:
    create table TBL
    (
    A VARCHAR2(3) not null,
    B VARCHAR2(10) not null,
    C VARCHAR2(5) not null,
    D VARCHAR2(1) not null,
    E VARCHAR2(4) not null,
    F VARCHAR2(4) not null,
    G VARCHAR2(6) not null,
    H VARCHAR2(1) not null,
    I VARCHAR2(1) not null,
    J VARCHAR2(5) not null,
    K VARCHAR2(6) not null,
    L VARCHAR2(1) not null,
    M TIMESTAMP(6) not null,
    N VARCHAR2(2) not null
    )
    The size of all columns is about 55 bytes.

    Binsize is 8192, it doesn't contains these actual fields size obviously.

    As you said, "You might have a char column specified in your control file but if you don't specify char(1) it allocates 255 bytes", if so, 13*255+size of timestamp is probably approachable to 8192 bytes in control file. However, how to specify a char column in control file as you said? If I don't specify that, the load operation of sqlldr is always not correct according to size of column?

    Thanks a lot.
  • 5. Re: sqlldr bindsize parameter
    652398 Newbie
    Currently Being Moderated

    Thanks.

     

    Table has 14 fields:

    create table TBL

    (

    A VARCHAR2(3) not null,

    B VARCHAR2(10) not null,

    C VARCHAR2(5) not null,

    D VARCHAR2(1) not null,

    E VARCHAR2(4) not null,

    F VARCHAR2(4) not null,

    G VARCHAR2(6) not null,

    H VARCHAR2(1) not null,

    I VARCHAR2(1) not null,

    J VARCHAR2(5) not null,

    K VARCHAR2(6) not null,

    L VARCHAR2(1) not null,

    M TIMESTAMP(6) not null,

    N VARCHAR2(2) not null

    )


    The size of all columns is about 55 bytes. Binsize is 8192 in control file, However, two lines import once a time when executing. Obviously, it is not "55" for actually. Control file is like:


    TRUNCATE

    INTO TABLE TBL

    FIELDS TERMINATED BY X'09'

    TRAILING NULLCOLS

    (A                       "upper(trim(:A))"

    ,B                       "upper(trim(:B))"

    ,C                       "upper(trim(:C))"

    ,D                       "upper(trim(:D))"

    ,E                       "upper(trim(:E))"

    ,F                       "upper(trim(:F))"

    ,G                       "upper(trim(:G))"

    ,H                       "upper(trim(:H))"

    ,I                       "upper(trim(:I))"

    ,J                       "upper(trim(:J))"

    ,K                       "upper(trim(:K))"

    ,L                       "upper(trim(:L))"

    ,M                       "upper(trim(:M))"

    ,N                       "upper(trim(:N))"

    )

     

    It is not "14*255" as you said.

     

    Could you give me some advice? Thanks a lot.

  • 6. Re: sqlldr bindsize parameter
    652398 Newbie
    Currently Being Moderated

    Thanks.

     

    Table has 14 fields:

    create table TBL

    (

    A VARCHAR2(3) not null,

    B VARCHAR2(10) not null,

    C VARCHAR2(5) not null,

    D VARCHAR2(1) not null,

    E VARCHAR2(4) not null,

    F VARCHAR2(4) not null,

    G VARCHAR2(6) not null,

    H VARCHAR2(1) not null,

    I VARCHAR2(1) not null,

    J VARCHAR2(5) not null,

    K VARCHAR2(6) not null,

    L VARCHAR2(1) not null,

    M TIMESTAMP(6) not null,

    N VARCHAR2(2) not null

    )


    The size of all columns is about 55 bytes. Binsize is 8192 in control file, However, two lines import once a time when executing. Obviously, it is not "55" for actually. Control file is like:


    TRUNCATE

    INTO TABLE TBL

    FIELDS TERMINATED BY X'09'

    TRAILING NULLCOLS

    (A                       "upper(trim(:A))"

    ,B                       "upper(trim(:B))"

    ,C                       "upper(trim(:C))"

    ,D                       "upper(trim(:D))"

    ,E                       "upper(trim(:E))"

    ,F                       "upper(trim(:F))"

    ,G                       "upper(trim(:G))"

    ,H                       "upper(trim(:H))"

    ,I                       "upper(trim(:I))"

    ,J                       "upper(trim(:J))"

    ,K                       "upper(trim(:K))"

    ,L                       "upper(trim(:L))"

    ,M                       "upper(trim(:M))"

    ,N                       "upper(trim(:N))"

    )

     

    It is not "14*255" as you said.

     

    Could you give me some advice? Thanks a lot.

  • 7. Re: sqlldr bindsize parameter
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    As previously stated, if you do not specify a size in your control file, like:

     

    D "upper(trim(:D))"

     

    then it defaults to 255, as if you had done:

     

    D char(255) "upper(trim(:D))"

     

    so if you want to limit the size, then you need to do something like:

     

    D char(1) "upper(trim(:D))"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points