| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS puzzler
Yes.
-----Original Message-----
From: Jeffery Stevenson [mailto:jeff_at_mpv.com]
Sent: Thursday, August 24, 2000 9:00 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: DBMS_STATS puzzler
Did this same CHIEF account create the procedure?
Jeff S.
-----Original Message-----
Sent: Wednesday, August 23, 2000 10:11 PM
To: Multiple recipients of list ORACLE-L
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-LReceived on Mon Aug 28 2000 - 07:13:57 CDT
(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: 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
 |
 |