Permission to gather_table_stats

[marist89]

I am trying to use dbms_stats.gather_table_stats for a particular partition of a partitioned table, but I keep getting an "insufficient privileges" message (See below). I can gather_table_stats on the entire table, but not on a partition. Any hints?

SQL> create user A identified by a
  2  temporary tablespace temp
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL> grant connect to a
  2  /

Grant succeeded.

SQL> create user b identified by b
  2  temporary tablespace temp
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL> grant connect, analyze any to b
  2  /

Grant succeeded.

SQL> connect a/a
Connected.
SQL> create table xyz (x number, y number, z number)
  2  partition by range (x) (
  3  partition p1 values less than (10), 
  4  partition p2 values less than (20),
  5  partition p3 values less than (maxvalue))
  6  /

Table created.

SQL> connect b/b
Connected.
SQL> exec dbms_stats.gather_table_stats(ownname=>'A',tabname=>'XYZ',partname=>'P2',cascade=>TRUE);
BEGIN dbms_stats.gather_table_stats(ownname=>'A',tabname=>'XYZ',partname=>'P2',cascade=>TRUE); END;

*
ERROR at line 1:
ORA-14506: LOCAL option required for partitioned indexes
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_STATS", line 4462
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_STATS", line 3564
ORA-01031: insufficient privileges
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats(ownname=>'A',tabname=>'XYZ',cascade=>TRUE);

PL/SQL procedure successfully completed.

It's probably not relevant, but I'm on 8.1.7.2 and Solaris 2.8.

[Mr.Hanky]

You may want to try and drop the "cascade=>TRUE" parameter since it is used for the corresponding index and the errors seem to point to that as the problem.

MH

[stecal]

I agree - an example from a note in Metalink shows that it works. The difference is the cascade clause. Here is a snippet from the article:

Example 2: Compute mode with statistics at the subpartition level only.
=========

SQl> execute dbms_stats.gather_index_stats('scott', 'idx_orders', partname => 'SYS_SUBP10367');


In that case, the statistics will be computed at the subpartition level only.

SQL> select index_name, PARTITION_NAME, SUBPARTITION_NAME, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY
from user_ind_subpartitions
where index_name = 'IDX_ORDERS'
order by PARTITION_NAME, SUBPARTITION_NAME;

INDEX_NAME PARTITION_NAME SUBPARTITION_NAME DISTINCT_KEYS CLUSTERING_FACTOR GLOBAL_STATS
---------- -------------- ----------------- ------------- ----------------- ------------
IDX_ORDERS Q1 SYS_SUBP10365 NO
IDX_ORDERS Q1 SYS_SUBP10366 NO
IDX_ORDERS Q2 SYS_SUBP10367 2 110 NO
IDX_ORDERS Q2 SYS_SUBP10368 NO
IDX_ORDERS Q3 SYS_SUBP10369 NO
IDX_ORDERS Q3 SYS_SUBP10370 NO
IDX_ORDERS Q4 SYS_SUBP10371 NO
IDX_ORDERS Q4 SYS_SUBP10372 NO

The other levels are not computed.

[marist89]

Originally posted by Mr.Hanky
You may want to try and drop the "cascade=>TRUE" parameter since it is used for the corresponding index and the errors seem to point to that as the problem.

MH

Yes, but it works just computing stats on the table with cascade=>TRUE...

[marist89]

Originally posted by Mr.Hanky
You may want to try and drop the "cascade=>TRUE" parameter since it is used for the corresponding index and the errors seem to point to that as the problem.

MH

Plus, it works as the owner...

SQL> connect a/a
Connected.
SQL> exec dbms_stats.gather_table_stats(ownname=>'A',tabname=>'XYZ',partname=>'P2',cascade=>TRUE);

It's got to be permissions, right?

[marist89]

Originally posted by marist89
It's probably not relevant, but I'm on 8.1.7.2 and Solaris 2.8.

It was relevent...
http://metalink.oracle.com/metalink/...G&p_id=1706144