RE: Problem running dbms_sql inside the procedure
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 16 Nov 2006 14:18:59 -0500
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@xxxxxxxxxxxxx]
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
______________________________________________________________________
Other related posts:
