dbms_stats.alter_database_tab_monitoring

While implementing my new statistics gathering procedure in production, I ran into a snag with permissions.

The very first step in my process is to setup my "analyze" user and create a job that runs every night that turns MONITORING on for any new tables. Sounds simple enough, right?

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 9 21:14:18 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> create user analyzer identified by youbetcha
2  temporary tablespace temp
3  default tablespace tools
4  quota unlimited on tools;

User created.

SQL> grant create session to analyzer;

Grant succeeded.

SQL> grant alter session to analyzer;

Grant succeeded.

SQL> grant execute on dbms_stats to analyzer;

Grant succeeded.

OK, that should be enough, right? Let's give it a try.

SQL> connect analyzer/youbetcha
Connected.

SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

Hmm. I looked at the package spec and find out it's run with Invoker Rights and not Definer's rights. Ah, I must need "ANALYZE ANY" privilege.

SQL> grant analyze any to analyzer;

Grant succeeded.

That must be it, let's try it again.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

OK, that ain't it. I did some research on Metalink, AskTom, and poured through the docs, but didn't really get anywhere. Then I thought, how about a Trace? I started a level 12 trace and then re-executed the procedure. Under the covers, dbms_stats.alter_database_tab_monitoring just does a "ALTER TABLE xyz MONITORING". I get it, I need "ALTER ANY TABLE".

SQL> connect / as sysdba
Connected.
SQL> grant alter any table to analyzer;

Grant succeeded.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);

PL/SQL procedure successfully completed.

Simple as that. Now all I have to do is create a dbms_job and run it every day:

SQL> declare
2     lJobNo INTEGER;
3     lJob   VARCHAR2(2222);
4  begin
5
6     lJob := 'dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);';
7     dbms_job.submit(
8        job=>lJobNo,
9        what=>lJob,
10        next_date=>SYSDATE,
11        interval=>'trunc(sysdate+1)');
12
13     dbms_output.put_line('job submitted: ' || to_char(lJobNo) || '...');
14
15  end;
16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, last_date, last_sec,
2     next_date, next_sec, broken,
3     failures, what
4  from user_jobs;

     JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B   FAILURES WHAT
---------- --------- -------- --------- -------- - ---------- -------------------------
      23 09-AUG-05 21:39:31 10-AUG-05 00:00:00 N          0 dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);                                        

That's done, on to the next step!