Re: DBMS_STATS puzzler

Not only is 8.1.6.1.1 out, but 8.1.6.2 came out just this week for AIX

Terry

Thomas Jeff wrote:

> I'm also on AIX 4.3.3, and:
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> PL/SQL Release 8.1.6.0.0 - Production
> CORE 8.1.6.0.0 Production
> TNS for IBM/AIX RISC System/6000: Version 8.1.6.0.0 - Production
> NLSRTL Version 3.4.0.0.0 - Production
>
> Interesting. 8.1.6.1? We had no idea that there was a patch release
> out for AIX??? We've certainly not received any notification.
>
> Thanks for the information.
>
>
> -----Original Message-----
> Sent: Thursday, August 24, 2000 9:00 AM
> To: ORACLE-L_at_fatcity.com; Thomas Jeff
>
> 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
> > (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 Fri Aug 25 2000 - 06:40:33 CDT