This content has been marked as final. Show 3 replies
Does the oracle user that owns the schema the given procedure is have the grants needed on all the objects in the other schemas? I think that's why it does not work for some schemas.
I did something like that only for objects in one schema and not in different schemas.
Edited by: BEDE on Mar 6, 2013 11:12 AM
You shouldn't go around rebuilding indexes like that..
Apart from that, get rid of at least 2 loops. You can get all statements with something like (not tested)
Please note that you probably also confused table owner and index owner.
SELECT 'alter index '||ai.owner_name||'.'||ai.index_name||' rebuild' AS alter_stmt FROM all_indexes ai, control_table3 ct, global_bu_mapping g WHERE (g.owner_name = ct.schema_name OR ct.schema_name = 'ALL') AND ai.table_owner = g.owner_name AND ai.table_name = g.schema_name;