Permission to gather_table_stats
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Permission to gather_table_stats

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    Question

    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?
    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.
    It's probably not relevant, but I'm on 8.1.7.2 and Solaris 2.8.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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.


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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...
    Code:
    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width