SQL*Loader In DIRECT Path Changes Index Status From VALID To UNUSABLE (Doc ID 267141.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.5 to 9.2.0.5 - Release: 9.2 to 9.2
IBM AIX on POWER Systems (64-bit)

Symptoms

SQL*Loader started in DIRECT path changes the index status from VALID to UNUSABLE as the next test demonstrates:

connect scott/tiger

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Apr 1 02:29:30 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

CREATE TABLE STG_CA_CA_DAY_RCD
(
   ACCOUNT_NUMBER VARCHAR2(30 BYTE),
   MIN_PAYMENT NUMBER(14,2),
   SOURCE_CD VARCHAR2(3 BYTE),
   PAYMENT_DUE_DATE DATE,
   LAST_PAID_DATE DATE,
   EXCESS_DUE_CODE VARCHAR2(1 BYTE),
   JOB_STATUS VARCHAR2(1 BYTE),
   BANK_CD NUMBER(1)
);

Table created.

CREATE INDEX STG_CA_CA_DAY_RCD_I ON STG_CA_CA_DAY_RCD (ACCOUNT_NUMBER) LOGGING;

Index created.

CREATE TABLE STG_CA_CA_DAY_RCD_T (RECORD_COUNT NUMBER);

Table created.

CREATE TABLE STG_CA_CA_DAY_RCD_H (SOURCE_NAME CHAR, FILE_DATE DATE);

Table created.

connect / as sysdba

Connected.

select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, status
from   sys.dba_indexes
where  table_name = 'STG_CA_CA_DAY_RCD';

TABLE_NAME        INDEX_NAME          INDEX_TYPE UNIQUENES STATUS
----------------- ------------------- ---------- --------- ------
STG_CA_CA_DAY_RCD STG_CA_CA_DAY_RCD_I NORMAL     NONUNIQUE VALID

-- Now start SQL*Loader (see below) and then:

select count(*) from STG_CA_CA_DAY_RCD;

COUNT(*)
----------
       139

select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, status
from   sys.dba_indexes
where  table_name = 'STG_CA_CA_DAY_RCD';

TABLE_NAME        INDEX_NAME          INDEX_TYPE UNIQUENES STATUS
----------------- ------------------- ---------- --------- --------
STG_CA_CA_DAY_RCD STG_CA_CA_DAY_RCD_I NORMAL     NONUNIQUE UNUSABLE


-- SQL*Loader was started wiuth command line:
sqlldr USERID=scott/tiger CONTROL=MAP_CA_CA_DAY_RCD.ctl LOG=test.LOG

-- SQL*L;Oadwer control file MAP_CA_CA_DAY_RCD.ctl
OPTIONS (DIRECT=TRUE,PARALLEL=FALSE, ERRORS=50, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
INFILE 'CA_D_CA_DAY_RCD_G30.DAT'
READBUFFERS 4
INTO TABLE STG_CA_CA_DAY_RCD
TRUNCATE
REENABLE DISABLED_CONSTRAINTS
WHEN
(1:1) = 'D'
FIELDS
(
"ACCOUNT_NUMBER" POSITION (2:11) CHAR ,
"MIN_PAYMENT" POSITION (12:25) DECIMAL EXTERNAL ,
"SOURCE_CD" POSITION (26:28) CHAR ,
"PAYMENT_DUE_DATE" POSITION (29:36) DATE "YYYYMMDD" ,
"LAST_PAID_DATE" POSITION (37:44) DATE "YYYYMMDD" ,
"EXCESS_DUE_CODE" POSITION (45:45) CHAR ,
"BANK_CD" POSITION (46:46) INTEGER EXTERNAL
)

INTO TABLE "SCOTT"."STG_CA_CA_DAY_RCD_H"
TRUNCATE
REENABLE DISABLED_CONSTRAINTS
WHEN
(1:1) = 'H'
FIELDS
(
"SOURCE_NAME" POSITION (2:11) CHAR ,
"FILE_DATE" POSITION (12:19) DATE "YYYYMMDD"
)

INTO TABLE "SCOTT"."STG_CA_CA_DAY_RCD_T"
TRUNCATE
REENABLE DISABLED_CONSTRAINTS
WHEN
(1:1) = 'T'
FIELDS
(
"RECORD_COUNT" POSITION (2:16) INTEGER EXTERNAL
)


-- Input data file CA_D_CA_DAY_RCD_G30.DAT
HCA 20050730
D0835910048 11163.73 2001040720010224 6
D0835910083 448.22 20010407 6
D0835910097 3940.00 20010419 6
D0836009231 1477.14 6
D1335910000 32205.00 20010407 6
D1335910046 1664.00 20010407 6
D1335910052 273.93 20010410 6
D1335910075 1444.00 20010406 6
D1335910098 6342.00 20010406 6
D2536000012 12354.45 6
D5935900525 149765.66 20010410 6
D5935900539 29662.00 2001032420010224 6
D5935900542 25047.00 20010419 6
D5935900556 42465.00 20010419 6
D5935900567 671.00 20010226 6
D5935900573 461.00 2001022620010224 6
D5935900590 13653.00 20010322 6
D5935900607 58464.00 20010324 6
D5935900610 54897.00 2001032420010224 6
D5935900624 157.00 20010322 6
D5935900638 18742.31 20010420 6
D5935900641 25299.00 20010322 6
D5935900655 62703.00 20010420 6
D5935900669 163.00 20010406 6
D5935900672 8282.00 2001032420010224 6
D5935900706 12422.00 2001032420010224 6
D5935900723 23216.00 2001032420010224 6
D5935900737 22402.47 20010331 6
D5935900740 358.00 20010410 6
...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms