2 Replies Latest reply: Oct 30, 2012 2:59 AM by user4421692 RSS

    ORA-01502

    user4421692
      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
          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
            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