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?
It's probably not relevant, but I'm on 8.1.7.2 and Solaris 2.8.Code: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.