ORA_01418, Index Issue - Need Help

[DSDexter]

Hi Gurus,

I am using the Oracle enterprise stage, With load option. I am specifying the Index mode property to rebuild. I am getting the following error. I searched for the forum for this topic but it didnt help much.

Code: Select all

ORA_Aft_Cpr_Data_Tab: Oracle call failed; sqlcode = -1418; message: ORA-01418: specified index does not exist
ORA_Aft_Cpr_Data_Tab: ExecuteImmediate failed for:alter index Index_Name1 rebuild parallel nologging compute statistics.
ORA_Aft_Cpr_Data_Tab: Index `Index_Name1' on table `WMCFI.AFT_CPR_DATA' has NOT been rebuilt.
ORA_Aft_Cpr_Data_Tab: Oracle call failed; sqlcode = -1418; message: ORA-01418: specified index does not exist
ORA_Aft_Cpr_Data_Tab: ExecuteImmediate failed for:alter index Index_Name2 rebuild parallel nologging compute statistics.

Also it shows that the indexes status as OK, although they are not rebuild.

Code: Select all

ORA_Aft_Cpr_Data_Tab: Index: Index_Name1; status: VALID.
Index: Index_Name2; status: VALID.
Index: Index_Name3; status: VALID.

Can any one suggest me what exactly is the problem.

[chulett]

Odd... have you run this problem by your DBA?

[DSDexter]

Chullet,

I think the User that loads this table doesnt have access to either disable or enable Index.

When I remove this Index Mode property from the stage. I no longer get this fatal error.Also If I check the status of the index, Its Valid.

So should I not use this property although there are indexes in the table?

In that case what is the use of this property?

[keshav0307]

you can use, and you should have access to do so.

[DSDexter]

Keshav,

Yes you are right that I need access to do a alter on the Index. But My question is, Its making no difference if I not use this property in the job.

Is this something to do with table partioning? Forgive me if I am wrong.

Our table is not partioned I guess....

[CLOPES]

Hello,
ORA-01418 gives http://www.techonthenet.com/oracle/errors/ora01418.php in the net.
Are you sure that your index exists ??
If yes i think it's an access problem.

[chulett]

I think the User that loads this table doesnt have access to either disable or enable Index.

When I remove this Index Mode property from the stage. I no longer get this fatal error.Also If I check the status of the index, Its Valid.

So should I not use this property although there are indexes in the table?

In that case what is the use of this property?

The "use" is only for users who have the grants they need to properly maintain the indexes. Most of the options available that speed processing will be useless if your user doesn't have the grants needed to support them. For example, it is trying to drop the indexes before the load and then rebuild them afterwards, it cannot so your load completes and the errors are logged but I'm assuming the load is taking longer than it could because of that. Of course, that depends on exactly how many and how complex your indexes are.

So yes, either get the grants you need or don't enable those options that you don't have the grants to support.

[DSDexter]

Hello,
ORA-01418 gives http://www.techonthenet.com/oracle/errors/ora01418.php in the net.
Are you sure that your index exists ??
If yes i think it's an access problem.

Thanks CLOPES for the prompt reply.

Yes I have checked the all_indexes and dba_indexes. Additionally I get more information about that Index from all_ind-columns view also. So all of this indicates that the Index does exists.

[DSDexter]

Thanks Chullet,

The Information you shared was really very helpfull for me and I hope will be for others also.

Yes, Your assumption is correct, My job is taking slightly more time to load the tables. The end user is Ok with the load time provided he doesnt see the red and yellow DOTS :D in the Job Log.