SQL*Loader Hangs forever sometimes

Question

I have SQL*Loader scripts I'm running against an 11g Database.
I am using the 11g version of SQL*Loader.

I am having an issue where SQL*Loader hangs after the last record is inserted, but before printing the final commit count to the command window and before printing to the log file.

This seems to work fine on a database local to our LAN, but hangs here when run on the Database outside our LAN.

If I kill the process manually, all records are loaded into the database successfully.

In the .BAT FIle:

sqlldr CONTROL='SOME_TABLE.ctl' log='logs/SOME_TABLE.log' bad='bad/SOME_TABLE.bad' skip=1

In the .CTL File:

OPTIONS(direct=true, rows=20000)
load data 
infile 'data/SOME_TABLE.csv'
append
into table SOME_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols (
    FIELD01     CHAR(38),
    FIELD02     CHAR(500),
    FIELD03     CHAR(34),
    FIELD04     CHAR(1),
    FIELD05     CHAR(1),
    FIELD06     CHAR(10),
    FIELD07     CHAR(2),
    FIELD08     CHAR(5),
    FIELD09     CHAR(2),
    FIELD10     CHAR(5),
    FIELD11     CHAR(5),
    FIELD12     CHAR(4),
    FIELD13     CHAR(2),
    FIELD14     CHAR(9),
    FIELD15     CHAR(9),
    FIELD16     TIMESTAMP      "YYYY-MM-DD HH24:MI:SS.FF6",
    FIELD17     TIMESTAMP      "YYYY-MM-DD HH24:MI:SS.FF6",
    FIELD18     CHAR(38),
    FIELD19     CHAR(2),
    FIELD20     TIMESTAMP      "YYYY-MM-DD HH24:MI:SS.FF6",
    FIELD21     CHAR(2),
    FIELD22     CHAR(38),
    FIELD23     DATE           "MM/DD/YYYY",
    FIELD24     CHAR(15),
    FIELD25     DATE           "MM/DD/YYYY",
    FIELD26     CHAR(15),
    FIELD27     CHAR(3),
    FIELD28     CHAR(5),
    FIELD29     CHAR(4),
    FIELD30     CHAR(10),
    FIELD31     CHAR(10),
    FIELD32     CHAR(1),
    FIELD33     CHAR(4),
    FIELD34     CHAR(1),
    FIELD35     CHAR(1),
    FIELD36     CHAR(1)
)

Table DDL:

--------------------------------------------------------
--  DDL for Table SOME_TABLE
--------------------------------------------------------
CREATE TABLE SOME_TABLE (
    FIELD01      NUMBER(*,0)         NOT NULL,
    FIELD02      FLOAT(126)          NOT NULL,
    FIELD03      VARCHAR2(34)            NULL,
    FIELD04      CHAR(1)                 NULL,
    FIELD05      CHAR(1)                 NULL,
    FIELD06      CHAR(10)                NULL,
    FIELD07      CHAR(2)                 NULL,
    FIELD08      CHAR(5)                 NULL,
    FIELD09      CHAR(2)                 NULL,
    FIELD10      CHAR(5)                 NULL,
    FIELD11      CHAR(5)                 NULL,
    FIELD12      CHAR(4)                 NULL,
    FIELD13      CHAR(2)                 NULL,
    FIELD14      CHAR(9)                 NULL,
    FIELD15      CHAR(9)                 NULL,
    FIELD16      TIMESTAMP(6)        NOT NULL,
    FIELD17      TIMESTAMP(6)        NOT NULL,
    FIELD18      NUMBER(*,0)             NULL,
    FIELD19      CHAR(2)                 NULL,
    FIELD20      TIMESTAMP(6)            NULL,
    FIELD21      CHAR(2)                 NULL,
    FIELD22      NUMBER(*,0)             NULL,
    FIELD23      DATE                    NULL,
    FIELD24      VARCHAR(15)             NULL,
    FIELD25      DATE                    NULL,
    FIELD26      VARCHAR(15)             NULL,
    FIELD27      CHAR(3)                 NULL,
    FIELD28      CHAR(5)                 NULL,
    FIELD29      CHAR(4)                 NULL,
    FIELD30      VARCHAR2(10)            NULL,
    FIELD31      VARCHAR2(10)            NULL,
    FIELD32      CHAR(1)                 NULL,
    FIELD33      CHAR(4)                 NULL,
    FIELD34      CHAR(1)                 NULL,
    FIELD35      CHAR(1)                 NULL,
    FIELD36      CHAR(1)                 NULL
)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE RAW_DATA_01_TS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                BUFFER_POOL      DEFAULT
               )
PARTITION BY RANGE(FIELD16) INTERVAL(NUMTODSINTERVAL(10, 'MINUTE')) (
    PARTITION SOME_TABLE_201001010000 VALUES LESS THAN (TO_DATE('2010-01-01 00:00', 'YYYY-MM-DD HH24:MI'))
);

Expected:

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Feb 17 10:36:14 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 252593.

Actual:

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Feb 17 10:36:14 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Setting DIRECT=FALSE seems to have fixed the hanging issue. Now I just need to know why it hangs when DIRECT=TRUE is use. It works fine against our local Database, but hangs for the largest ones on remote databases. — ScrappyDev, Feb 22 '12 at 13:54
Network latency matters quite a bit when talking about database operations. Databases on your local LAN will run significantly faster than those located remotely. — Nick, May 13 '12 at 16:10

score 0 · Answer 1 · answered Feb 18 '12 at 08:29

0

I suspect this is linked to DIRECT=TRUE. Oracle has to revalidate indexes and constraints at the end of the process. If your table is very large, this can be as long.

answered Feb 18 '12 at 08:29

Benoit

76,634
23
210
236

There aren't' any Indexes or Constraints on this table. Unless you're saying that there are hidden indexes for the partitioning? – ScrappyDev Feb 20 '12 at 23:27
@scrappythenell: Maybe not… have you tried removing DIRECT=TRUE and measuring duration? – Benoit Feb 21 '12 at 06:06
Setting `DIRECT=FALSE` seems to have fixed the hanging issue. Now I just need to know why it hangs when `DIRECT=TRUE` is use. – ScrappyDev Feb 22 '12 at 13:52
I don't understand half of it, but… http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_modes.htm – Benoit Feb 22 '12 at 14:10
You actually do have constraints on the table. There are several columns defined as "not null". Oracle is validating those columns are not null once the data is inserted. – Nick May 13 '12 at 16:07

SQL*Loader Hangs forever sometimes

1 Answers1