Privileges for DBMS_STATS.EXPORT_TABLE_STATS

Hi,

2-node RAC cluster, 32-bit 10.2.0.3

I'm trying to write some PL/SQL that will "roll-over" the stats from one partition to the next in tables partitioned by range on a date column.

Because the month is different for every partition, I need to export the stats from the source partition, update at least the min and max values on any date columns (and maybe more?), then import them into the destination partition.

I'm having some trouble with the export. In our DB, there's a user with the DBA role granted ("MyDBA") and several users that own date-range-partitioned tables (e.g. "TableOwner1"). If, as MyDBA, I call:

    dbms_stats.export_table_stats(ownname => <TableOwner1>

,tabname => <PARTITIONED_TABLE>
,partname => <CURRENT_PARTITION>
,statid => <SOME_ID>
,cascade => true
,statown => <MyDBA>
,stattab => <MY_STATS_TABLE>);

the result depends on how it is called. If called in an anonymous block, the call succeeds. If called in a stored proc, the call fails, with:

ORA-20000: TABLE "TABLEOWNER1"."PARTITIONED_TABLE" does not exist or insufficient privileges.

So I surmise that it's a privilege problem, and that MyDBA has some privilege on "TABLEOWNER1"."PARTITIONED_TABLE" due to the DBA role grant, but has not had that privilege granted directly.

Also, the routine runs fine as SYS, but fails in the same way if run as SYSTEM. My next step was to explicitly GRANT ALL on PARTITIONED_TABLE to MyDBA as the TableOwner1 user. I double-checked in DBA_TAB_PRIVS, and MyDBA has 11 privileges granted to it on PARTITIONED_TABLE. So I tried the stored proc again, but it fails in the same place with the same exception. On the off-chance that Oracle was mis-reporting a different privs problem, I also explicitly granted execute on DBMS_STATS to MyDBA, but that didn't help either.

Is there a table privilege that is not granted in a GRANT ALL? I'm out of ideas as to what additional privilege could be missing. What have I missed?

Thanks for any suggestions,
James

The content of this e-mail is confidential and may be privileged. It may be read, copied and used only by the intended recipient and may not be disclosed, copied or distributed. If you received this email in error, please contact the sender immediately by return e-mail or by telephoning +44 20 7260 2000, delete it and do not disclose its contents to any person. You should take full responsibility for checking this email for viruses. Markit reserves the right to monitor all e-mail communications through its network. Markit and its affiliated companies make no warranty as to the accuracy or completeness of any information contained in this message and hereby exclude any liability of any kind for the information contained herein. Any opinions expressed in this message are those of the author and do not necessarily reflect the opinions of Markit. For full details about Markit, its offerings and legal terms and conditions, please see Markit's website at http://www.markit.com <http://www.markit.com/> .

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