problem running export_schema_stats on different schema

From: M <michaelkatsilis_at_yahoo.com>
Date: 11 Nov 2004 21:44:40 -0800
Message-ID: <48691fdc.0411112144.2a3dfee9@posting.google.com>

Hi,

Question: What privileges do I need to run DBMS_STATS.EXPORT_SCHEMA_STATS routine?

I need to export the stats for a schema that is not mine. If I run it using the schema owner POWNER (see below) it works, but DBMS_STATS.EXPORT_SCHEMA_STATS will not allow the export of stats when running it as myself (MK see below).

Database is Oracle 8.1.7 running on AIX.

I'm not a dba, just a developer using a script we wrote to export database snapshots for testing our changes. I've spoken to 2 DBA's about this and tried a host of things that didn't work (eg. GRANT ANALYZE ANY TO MK and so on), please think about the problem before posting a suggestion as it does not seem as easy as it sounds to solve. Creating the stats table in my schema works fine, but always get the following error when the DBMS_STATS.EXPORT_SCHEMA_STATS routine is executed...

BEGIN
*
ERROR at line 1:
ORA-20000: Schema POWNER does not exist or insufficient privileges to analyze
an object within it

ORA-06512: at "SYS.DBMS_STATS", line 3028
ORA-06512: at "SYS.DBMS_STATS", line 3050
ORA-06512: at "SYS.DBMS_STATS", line 3326
ORA-06512: at line 2

Here is a code sample...

• this code works fine BEGIN DBMS_STATS.CREATE_STAT_TABLE ('MK', 'FULL_PROD_STATS', NULL); END;
• Problem occurs here... BEGIN SYS.DBMS_STATS.EXPORT_SCHEMA_STATS ('POWNER', 'MK.FULL_PROD_STATS'); END;

Any help would be much appreciated.

Regards,

M Received on Thu Nov 11 2004 - 23:44:40 CST