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: Problem running dbms_sql inside the procedure

RE: Problem running dbms_sql inside the procedure

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 16 Nov 2006 14:18:59 -0500
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51801DBB1A9@ex2003.metratech.com>


Error was in the calling block so fixed it.

"(e) Are you aware that with method_opt=> 'FOR ALL COLUMNS SIZE AUTO' you are generating a ton of, largely unnecessary and potentially dangerous (for performance), histograms?"

I am still doing testing on this option, but am not that was a bug in 9i and got fixed in 10.2.0.2 where it is now doing better job.  

Also what is the option that you have set or recommend? Also it will be great of you can post some results that you gather while testing this option.    


From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Thursday, November 16, 2006 1:37 PM To: Harvinder Singh
Cc: oracle-l
Subject: Re: Problem running dbms_sql inside the procedure  

(a) What Oracle version?
(b) What is the error you are getting?
(c) It could be that you have some required privileges granted through a
role which does not work from within a stored proce.
(d) Why the use of dynamic sql instead of simply

   begin
     dbms_stats.gather_schema_stats(ownname=>user, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');

   end;
(e) Are you aware that with method_opt=> 'FOR ALL COLUMNS SIZE AUTO' you
are generating a ton of, largely unnecessary and potentially dangerous
(for performance), histograms?

At 11:14 AM 11/16/2006, Harvinder Singh wrote:

Hi,  

We are getting invalid sql error when running this procedure but when I capture of v_sql during debgging and run that statement it works fine.What can be the possible issue?  

CREATE OR REPLACE PROCEDURE analyze_all_tables AS
v_user_name varchar2 (30 );
v_sql varchar2 (4000 );
begin
SELECT sys_context( 'USERENV' , 'SESSION_USER' ) into v_user_name FROM dual;

v_sql := 'begin dbms_stats.gather_schema_stats( 
ownname=> ''' || v_user_name || ''', 
cascade=> TRUE, 

method_opt=> ''FOR ALL COLUMNS SIZE AUTO'', options=> ''GATHER''); end;';
execute immediate v_sql;
end;
/  

Thanks
--Harvinder  

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com/>



This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 16 2006 - 13:18:59 CST

Original text of this message

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