-----Original Message-----
From: Fuad Arshad [mailto:fuadar@yahoo.com]
Sent: Tuesday, June 13, 2006 12:45 PM
To: Smith, Steven K - MSHA; oracle-l
Subject: Re: Job to import system statistics - errors

grant your id  execute priviliege directly to dbms_stats and it should work.

had the same issue

----- Original Message ----
From: "Smith, Steven K - MSHA" <Smith.Steven@DOL.GOV>
To: oracle-l <oracle-l@freelists.org>
Sent: Tuesday, June 13, 2006 1:29:54 PM
Subject: Job to import system statistics - errors

I'm stuck.  Pulling my hair out.

 

I have collected system statistics for day, night and weekend processing. 

 

I can interactively import system statistics with no problems:

 

SQL->execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'SYSTEM_STAT_TABLE', statid => 'DAYTIME', statown => 'SYSTEM');

 

PL/SQL procedure successfully completed.

 

When I put that in an oracle job, It errors and the trace file is:

 

ORA-12012: error on auto execute of job 2219
ORA-20000: Unable to import system statistics stats from user stat table SYSTEM.
SYSTEM_STAT_TABLE: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 5575
ORA-06512: at line 1
*** 2006-06-13 09:55:33.659
ORA-12012: error on auto execute of job 2219
ORA-20000: Unable to import system statistics stats from user stat table SYSTEM.
SYSTEM_STAT_TABLE: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 5575
ORA-06512: at line 1

 

 

I have given myself explicit privileges to the system.system_stat_table:

 

  1  select grantee, owner, table_name, privilege
  2  from dba_tab_privs
  3* where table_name = 'SYSTEM_STAT_TABLE'

 

GRANTEE                        OWNER           TABLE_NAME                PRIVILEGE
------------------------------ --------------- ------------------------- --------------------
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         ALTER
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         DELETE
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         INDEX
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         INSERT
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         SELECT
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         UPDATE
SSMITH                         SYSTEM          SYSTEM_STAT_TABLE         REFERENCES

II also have granted ssmith GATHER_SYSTEM_STATISTICS role.

 

Any ideas?

 

Steve