Privileges for DBMS_STATS.EXPORT_TABLE_STATS

From: "James Barton" <james.barton@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Sat, 1 Nov 2008 13:40:11 -0000
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/> .
--
//www.freelists.org/webpage/oracle-l
Privileges for DBMS_STATS.EXPORT_TABLE_STATS

Other related posts:
