RE: DBMS_STATS puzzler

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Thu, 24 Aug 2000 09:00:15 -0500
Message-Id: <10599.115475@fatcity.com>

Hi Jeff,
What version of oracle are you running ? It worked for me on an 8.1.6.1.0 database on AIX 4.3 (with PL/SQL version 8.1.6.1.0).
The account was given 'analyze any' privilege. SQL>get mytest.spc
  1 CREATE OR REPLACE PROCEDURE MYTEST
  2 AS
  3 BEGIN
  4 DBMS_STATS.GATHER_TABLE_STATS (OWNNAME =>'DBM',

  5                                   TABNAME =>'CUSTOMER_LISTINGS',
  6                                   PARTNAME =>'CUST_LIST_99',
  7                                   ESTIMATE_PERCENT => 30,
  8                                   BLOCK_SAMPLE => TRUE,
  9                                   METHOD_OPT => 'FOR COLUMNS SIZE 1',
 10                                   DEGREE => 1,
 11                                   GRANULARITY => 'PARTITION',
 12                                   CASCADE => TRUE,
 13                                   STATTAB => NULL,
 14                                   STATID => NULL,
 15                                   STATOWN => NULL
 16                                   );

 17* END;
SQL> / Procedure created.

SQL> exec mytest;

PL/SQL procedure successfully completed.

SQL>

• Kirti

> -----Original Message-----
> From: Thomas Jeff [SMTP:ThomasJe_at_tce.com]
> 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
Received on Thu Aug 24 2000 - 09:00:15 CDT