Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS puzzler

RE: DBMS_STATS puzzler

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Thu, 24 Aug 2000 07:59:57 -0500
Message-Id: <10599.115461@fatcity.com>


Did this same CHIEF account create the procedure?

Jeff S.

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

Yes, the CHIEF account does have ANALYZE ANY granted directly. In fact, the CHIEF account has a package that consist of wrappers for DBMS_STATS routines and this package works fine. Here, I'm testing statistics generation for for partitions, and ran into this buzzsaw. I executing both anonymous and the JDT procedure thru this same CHIEF account.

-----Original Message-----
Sent: Wednesday, August 23, 2000 4:35 PM To: Multiple recipients of list ORACLE-L

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-----
Sent: Wednesday, August 23, 2000 2:55 PM To: Multiple recipients of list ORACLE-L

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). -- Author: Jeffery Stevenson INET: jeff_at_mpv.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). -- 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 Thu Aug 24 2000 - 07:59:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US