RE: Job to import system statistics

From: Smith, Steven K - MSHA <Smith.Steven_at_DOL.GOV>
Date: Tue, 13 Jun 2006 12:47:59 -0600
Message-ID: <A49A36C009B8884C9246B36A0DA7923F02A9C80B@msha-lak-exmb01.msha.dir.labor.gov>

That's already done.. Didn't help.

GRANTEE                        OWNER                          TABLE_NAME
------------------------------ ------------------------------
------------------------------
GRANTOR                        PRIVILEGE

GRA HIE

------------------------------ ----------------------------------------
--- ---
SSMITH                         SYS                            DBMS_STATS
SYS                            EXECUTE

NO NO

Steve Smith

Envision Technology Partners / MSHA MSIS Team

Desk: 303-231-5499

-----Original Message-----
From: Fuad Arshad [mailto:fuadar_at_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_at_DOL.GOV> To: oracle-l <oracle-l_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 13 2006 - 13:47:59 CDT

RE: Job to import system statistics - errors