Hi, We are using version 11.2.0.4 of Oracle. We have one requirement in which we have multiple applications owning more than one schema in our database. Now we want them to gather stats on the related objects which they own , mainly the data load type of tables. The ones which were truncate+load kind of table. So stats should be gathered immediately after the load happens.
For this to achieve, we are seeing some issue , say schema1 , schema2, schema3 were used by application-1. But to gather stats through one procedure(PRC1) from schema1 , they need to have ANALYZE ANY privilege given to schema1. As because there were objects residing on schema2, schema3 which otherwise wont be allowed to be gathered from procedure P1 which is created on schema1 unless until schema1 has the ANALYZE ANY privilege. At same time this(analyze any) also adds some elevated privilege to schema1, through which it would be able to gather stats for all the schema's in the databases(schema4, schema5 etc) which are aligned to other application. So how to handle the scenario, such that schema1 can be restricted to gather stats privilege for objects of schema1, schema2, schema3 those are part of application-1? Creating three different procedures like PRC1 in each schema -1, 2,3 is also seems lot of duplication. So, Is there any other better option available?