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
Procedure is having access on all schemas.
ALL means 30 schemas we can get it from
SELECT owner_name FROM global_bu_mapping;
Apart from 30 we have some other schemas. The schema name should be taken from control table.
Please help me how to implement this logic.
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;