This discussion is archived
2 Replies Latest reply: Oct 30, 2012 12:59 AM by user4421692 RSS

ORA-01502

user4421692 Newbie
Currently Being Moderated
Hi,

I have this table

drop table IP_NUM cascade constraints;
create table IP_Site(
ID NUMBER NOT NULL,
Sigla VARCHAR2(10) ,
UserIns VARCHAR2(10) ,
UserUpd VARCHAR2(10) ,
Note VARCHAR2(100) ,
Provincia VARCHAR2(2) ,
ID_Rete VARCHAR2(50) ,
DateIns DATE ,
DateUpd DATE)
TABLESPACE &tspace;

ALTER TABLE IP_Site
add CONSTRAINT IP_Site_pk PRIMARY KEY (ID_Site);

I check the index

SELECT * from USER_INDEXES WHERE INDEX_NAME ='ID_PK'

I see the status set as 'VALIDATE' .


I have a sqlldr file like this:

1/|API/|/|/|/|MI/|OPEN/|2004-09-13 00:00:00/|
1/|API/|/|/|/|MI/|OPEN/|2004-09-13 00:00:00/|
2/|API/|/|/|/|MI/|OPEN/|2004-09-13 00:00:00/|


When I run the sqlldr, I have 3 record inside the table, and when I try some insert, I have this error:

ORA-01502: index 'TIP_WORKING.ID_PK' or partition of such index is in unusable state.

When I check the index

SELECT * from USER_INDEXES WHERE INDEX_NAME ='ID_PK'

I see the status set as 'UNUSABLE' .

I have 2 question:

1 - Is it possibile that sqlldr can violate a pk constraint?
2 - How I can resolve this problem?

Best regards and thanks for all
  • 1. Re: ORA-01502
    damorgan Oracle ACE Director
    Currently Being Moderated
    And the SQL Loader script contains DIRECT=TRUE?

    If not ... post the script.

    We can not help you with what we can not see.
  • 2. Re: ORA-01502
    user4421692 Newbie
    Currently Being Moderated
    Hi,

    this ii my .ctl file:

    LOAD DATA
    INFILE './NSU/CSV/IP_Site.csv'
    INTO TABLE IP_Site
    REPLACE
    FIELDS TERMINATED BY "/|"
    TRAILING NULLCOLS
    (
    ID CHAR,
    Sigla CHAR,
    UserIns CHAR,
    UserUpd CHAR,
    Note CHAR,
    Provincia CHAR,
    ID_Rete CHAR,
    DateIns DATE "YYYY-MM-DD HH24:MI:SS",
    DateUpd DATE "YYYY-MM-DD HH24:MI:SS"
    )


    and thi is my lanch string:

    sqlldr userid=$USR/$PAS@$SID control=./$DOM/IP_.ctl log=./LOG/"$DOM"_IP_"$DATA".log bad=./LOG/"$DOM"_IP_"$DATA".bad direct=Y errors=1000000

Legend

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