RE: Strange behavior with dbms

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Thu, 11 Dec 2003 08:24:27 -0800
Message-ID: <F001.005D98F0.20031211082427@fatcity.com>

Um...the PL/SQL script at the bottom of your e-mail doesn't DO anything other than output a line. Do you use this in a subsequent command file?

Cheers,
Mike

-----Original Message-----
Sent: Thursday, December 11, 2003 8:09 AM To: Multiple recipients of list ORACLE-L

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;
/

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Vergara, Michael (TEM)
INET: mvergara_at_guidant.com

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: 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). Received on Thu Dec 11 2003 - 10:24:27 CST

RE: Strange behavior with dbms_stats...