If you are on 8i or higher, it is better to use the dbms_stats utility
like this :

select
'execute dbms_stats.gather_index_stats('||''''||owner||''''||',
'||''''||index_name||''''||') ;'
from dba_indexes
where owner = 'DIC';

If not, then

select 'analyze index '||owner||'.'||index_name||' compute statistics;'
from dba_indexes
where owner = '<OWNER>';

will do the trick

You must supply the schema owner in <OWNER>

Hans

-----Original Message-----
From: Katz Gil
[mailto:oracledba-ezmlmshield-x3963433.[Email address protected]
Sent: woensdag 31 mei 2006 13:16
To: LazyDBA Discussion
Subject: analyze index


Hi

I want to analyze all the indexes in my schema

I know that the sql statement to get the indexes list is

SELECT index_name FROM dba_indexes WHERE
tablespace_name=<tablespace_name>;

And to analyze index I have to

analyze index <schema>.<index_name> validate structure;

how do I combine the two?



Gil Katz

QA Engineer

AppSight J2EE Team

Identify Software Ltd.

UIN 1538854

Tel: +972 (3) 9101927

=================================================

= So Long, and Thanks for All the Fish
=

=================================================







--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
-----------------------------------------------------------------
ATTENTION:
The information in this electronic mail message is private and
confidential, and only intended for the addressee. Should you
receive this message by mistake, you are hereby notified that
any disclosure, reproduction, distribution or use of this
message is strictly prohibited. Please inform the sender by
reply transmission and delete the message without copying or
opening it.

Messages and attachments are scanned for all viruses known.
If this message contains password-protected attachments, the
files have NOT been scanned for viruses by the ING mail domain.
Always scan attachments before opening them.
-----------------------------------------------------------------


Oracle LazyDBA home page