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.
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.
DSDexter wrote: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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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.
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.