Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jesica.

Asked: September 12, 2016 - 10:40 am UTC

Last updated: September 12, 2016 - 12:49 pm UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi,

We have a database with multiple schemas and we need a proactive gather stats. The idea would be to have one schema(CORE) to gather stats for all tables in all schemas, so everything is centralize. is there any possibility to do this without giving the ANALYZE ANY privilege to the CORE schema?

and Chris said...

As the docs say:

To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.


http://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68582

So it looks like the answer is no. But of course, there's always more than one way do to things...

You could create a procedure in each schema that gathers stats. Then grant your core schema access to these procedures. Provided you've used definers rights (the default) this will work fine:

conn hr/hr

create or replace procedure gather_stats is
begin
 dbms_stats.gather_table_stats('HR', 'EMPLOYEES');
end gather_stats;
/

grant select on hr.employees to scott;
grant execute on gather_stats to scott;

conn scott/scott

exec hr.gather_stats;
      
select last_analyzed, sysdate from all_tables
where  owner = 'HR'
and    table_name = 'EMPLOYEES';

LAST_ANALYZED  SYSDATE    
12-SEP-16      12-SEP-16


Or you could save yourself some hassle and leave gathering to the automatic stats job!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library