Re: DBMS_STATS puzzler

My first thought was a permissions problem.... But you aren't changing users when running it as a stored proc or anonymously. But.... I'll stick with my first thought. Has SYS granted execute on the DBMS_STATS package to the CHIEF user?

Other then that, I would think someone else on the list can help you. Lyall Barbour

At 11:54 AM 8/23/00 -0800, you wrote:
>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.
>
>SQL>
>
>
>--
>Author: Thomas Jeff
> INET: ThomasJe_at_tce.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Received on Wed Aug 23 2000 - 15:02:18 CDT