RE: DBMS_STATS puzzler

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Wed, 23 Aug 2000 15:04:05 -0500
Message-Id: <10598.115425@fatcity.com>

Does the person that the procedure run as have the ANALYZE ANY system privilege granted directly to them (and not through a role)? The fact that it will run as an anonymous PL/SQL block would tend to indicate this (because it will use role privileges when executing). HTH

Jeffery Stevenson
Chief Database Geek
Medical Present Value, Inc.
Austin, TX

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Thomas Jeff Sent: Wednesday, August 23, 2000 2:55 PM To: Multiple recipients of list ORACLE-L Subject: DBMS_STATS puzzler

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

Received on Wed Aug 23 2000 - 15:04:05 CDT