Tuesday, August 09, 2005

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!

3 comments:

yas said...

Why do you run this everyday? Is enabling monitoring once not enough?

Jeff Hunter said...

Why do you run this everyday? Is enabling monitoring once not enough?
This picks up any new tables that were created yesterday.

Anonymous said...

I had a similar problem. This solved it very nicely. Thanks!