DBMS_STATS puzzler

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Wed, 23 Aug 2000 13:46:48 -0500
Message-Id: <10598.115413@fatcity.com>

Any idea as to why my call to DBMS_STATS blows up when it's a compiled PL/SQL, but
works fine when I call it via anonymous PL/SQL? I've verified via DBA_TAB_PARTITIONS
that the statistics are being generated throught the anonymous call.

SQL> CREATE OR REPLACE PROCEDURE JDT AS

     BEGIN
       DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'MSDS', TABNAME =>


'INVOICE_HEADER', 

         PARTNAME => 'FY1995',  ESTIMATE_PERCENT => 20, BLOCK_SAMPLE =>
TRUE, 
         METHOD_OPT => 'FOR COLUMNS SIZE 1', DEGREE => 1, GRANULARITY =>


'PARTITION', 

         CASCADE => TRUE, STATTAB => NULL, STATID => NULL, STATOWN => NULL);
     END;

   /

Procedure created.

SQL> exec jdt;
BEGIN jdt; END;
*
ERROR at line 1:

ORA-14506: LOCAL option required for partitioned indexes
ORA-06512: at "SYS.DBMS_DDL", line 161
ORA-06512: at "SYS.DBMS_STATS", line 4420
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_STATS", line 3559
ORA-01031: insufficient privileges
ORA-06512: at "CHIEF.JDT", line 3
ORA-06512: at line 1

SQL> BEGIN
  2 DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'MSDS', TABNAME =>
'INVOICE_HEADER',

       PARTNAME => 'FY1995',  ESTIMATE_PERCENT => 20, BLOCK_SAMPLE => TRUE, 
       METHOD_OPT => 'FOR COLUMNS SIZE 1', DEGREE => 1, GRANULARITY =>


'PARTITION', 

       CASCADE => TRUE, STATTAB => NULL, STATID => NULL, STATOWN => NULL);
     END;
     /

PL/SQL procedure successfully completed. Received on Wed Aug 23 2000 - 13:46:48 CDT