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

Ok, here is the solution...thanks to me for my help...

BEGIN
SYS.DBMS_STATS.EXPORT_SCHEMA_STATS ('POWNER',
'FULL_PROD_STATS'
NULL,
USER);
END;

> -- Problem occurs here...
> BEGIN
> SYS.DBMS_STATS.EXPORT_SCHEMA_STATS ('POWNER',
> 'MK.FULL_PROD_STATS');
> END;

Ok, here is the solution...thanks to me for my help...

BEGIN
SYS.DBMS_STATS.EXPORT_SCHEMA_STATS ('POWNER',
'FULL_PROD_STATS',
NULL,
USER);
END;

> -- Problem occurs here...
> BEGIN
> SYS.DBMS_STATS.EXPORT_SCHEMA_STATS ('POWNER',
> 'MK.FULL_PROD_STATS');
> END;