ORA-01502 error

[taz75]

Firstly I have very little knowledge of Oracle so if any of you answer me can you do it simply! Thanks....

So, I had to modify an Oracle Table [valcompany] to increase the size of a column. This was OK and the structure was changed to this:

COLUMN NAME TYPE LENGTH NULLABLE
------------------ ---------- ---------- ----------
VC_CRO CHAR 8 NOT NULL
VC_JOB_NUMBER CHAR 4 NOT NULL
VC_VCODE_OLD VARCHAR2 2 NOT NULL
VC_VDATE_OLD DATE 7
VC_VCODE_NEW VARCHAR2 2
VC_VDATE_NEW DATE 7
VC_OPERATOR VARCHAR2 4

INDEX NAME COLUMN NAME POSITION
---------------------------- ---------------- --------
I_VALCOMPANY_CRO_JOBNUMBER VC_CRO 1
I_VALCOMPANY_CRO_JOBNUMBER VC_JOB_NUMBER 2

Now when I try and select data from this table where vc_cro='00000009' it gives the following error:

ORA-01502: index 'CONDUIT.I_VALCOMPANY_CRO_JOBNUMBER' is in direct load state

Can someone tell me how to DROP and then RE-CREATE this index (on VC_CRO + VC_JOB_NUMBER) or if there is another way to get round it.
Thanks in advance for any help,

Laura

 

[jimbopalmer]

Drop index CONDUIT.I_VALCOMPANY_CRO_JOBNUMBER;

create index CONDUIT.I_VALCOMPANY_CRO_JOBNUMBER on CONDUIT.VALCOMPANY(VC_CRO, VC_JOB_NUMBER) TABLESPACE whatever_index_tablespace_you_use; The one thing you can't give for your heart's desire is your heart. - Lois McMaster Bujold

 

[Guest_imported]

Hi !

Alter index CONDUIT.I_VALCOMPANY_CRO_JOBNUMBER rebuild;

cu