Re: table fragment and index rebuild time

On Jan 24, 11:47 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> On Jan 24, 2:07 pm, "sybrandb" <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jan 24, 6:39 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
> > > Hi all,
> > > I want to know, is there any way to find which tables need to be
> > > fragmented, and which index need to be rebuild.
> > > One by one I can find out this by using dbms_space package for tables
> > > (find unused blocks) and
> > > analyze index < index_name > validate structure;
> > > command and then see index_stats and find that.
>
> > > But I have 5 lacs tables and many indexes, how to know which schema to
> > > refresh.
> > > Right now we do that but based according period of work on any schema
> > > or wait till the performance is get down then we find it and do it.if you use LMTs fragmentation is a thing of the past.
> > I would rather spend my time on reading the Concept manual.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBADo we not have to refresh our tables and rebuild our indexes in oracle
> 9i.
> What you are talking about Tablespaces, but I am not. I am talking
> about tables and indexes in ORACLE 9i.- Hide quoted text -- Show quoted text -

I'm not talking about tablespaces only.
Generally speaking regularly rebuilding tables and indexes is a waste of both human and computer resources.
You would only need to rebuild something if that would *solve* something.
Usually rebuilding doesn't resolve anything. The futility of rebuilding indexes has been discussed here many times. Please search the archives.

-- 
Sybrand Bakker
Senior Oracle DBA