put it into a a dbms_output to see what is passed as variables
then wrap it in execute immediate. your doing dynamic pl/sql. i think that will work.
>
> From: anu <[EMAIL PROTECTED]>
> Date: 2003/12/11 Thu AM 11:54:35 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: Strange behavior with dbms_stats...
>
> The proc generates the 'exec dbms_stats ' statements for all the users. Are you
> saving the output and running it manually or not. IT would have the same statements
> that you run one by one.
>
> Jose Luis Delgado <[EMAIL PROTECTED]> wrote:List...
>
> SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).
>
> I use the proc at the bottom to generate statistics.
>
> It seems to work, but if I check statistics with:
>
> select owner, table_name, num_rows, blocks,
> av_row_len,
> to_char(last_analyzed, 'MM/DD/YYYY HH24:MI:SS')
> from dba_tables;
>
> the tables have the OLD last_analyzed time!
>
> but... If I execute ONE by ONE:
>
> exec dbms_stats.gather_schema_stats(ownname =>
> 'PERFSTAT', cascade => TRUE);
>
> it works fine!!...
>
> So, am I doing something wrong?
>
> Any help?
>
> TIA
> JL
>
> create or replace procedure get_statistics as
> cursor get_users_list is
> select username
> from dba_users
> where username != 'SYS'
> and username != 'SYSTEM';
>
> begin
> for i in get_users_list
> loop
> dbms_output.put_line('exec
> dbms_stats.gather_schema_stats(ownname =>
> '||chr(39)||i.username||chr(39)||', cascade =>
> TRUE);');
> end loop;
> end;
> /
>
>
>
> __________________________________
> Do you Yahoo!?
> New Yahoo! Photos - easier uploading and sharing.
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jose Luis Delgado
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).
>
>
> ---------------------------------
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
>
The proc generates the 'exec dbms_stats ' statements for all the users. Are you saving the output and running it manually or not. IT would have the same statements that you run one by one.
Jose Luis Delgado <[EMAIL PROTECTED]> wrote:
Jose Luis Delgado <[EMAIL PROTECTED]> wrote:
List...
SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).
I use the proc at the bottom to generate statistics.
It seems to work, but if I check statistics with:
select owner, table_name, num_rows, blocks,
av_row_len,
to_char(last_analyzed, 'MM/DD/YYYY HH24:MI:SS')
from dba_tables;
the tables have the OLD last_analyzed time!
but... If I execute ONE by ONE:
exec dbms_stats.gather_schema_stats(ownname =>
'PERFSTAT', cascade => TRUE);
it works fine!!...
So, am I doing something wrong?
Any help?
TIA
JL
create or replace procedure get_statistics as
cursor get_users_list is
select username
from dba_users
where username != 'SYS'
and username != 'SYSTEM';
begin
for i in get_users_list
loop
dbms_output.put_line('exec
dbms_stats.gather_schema_stats(ownname =>
'||chr(39)||i.username||chr(39)||', cascade =>
TRUE);');
end loop;
end;
/
__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jose Luis Delgado
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
