RE: table reorganizations

> I'm surprised at these responses.

Please don't take offense. It appears that you have received some very informative answers in addition to the facetious ones. ( which I *did* expect. Mladen never lets me down. )

To reiterate the point of responses to your question:

Running a SQL query to indicate that a table should be reorged to based upon the amount of free space it finds simply doesn't supply enough data to indicate that it's actually necessary. It probably isn't.

There are a lot of tuning "authorities" that make hard and fast rules about how to find problem areas by simply running a few queries.

It is unfortunately, not that simple. Or for people like Cary Millsap, Gary Goodman, Steve Adams, Jonathan Lewis and a number of others, it is, fortunately for them, not that simple. ;)

Jared

On Wed, 2004-01-07 at 12:49, Shrake, Jolene wrote:
> I'm surprised at these responses. I'm asking what sql statement most
> people use to identify tables that need reorganization because of
> "holes".
>
> We had an Oracle consultant here and he uses
>
> Select table_name,
> blocks-((num_rows*avg_row_len/<block_size>)*(1+(pct_free/100))) blkdiff
> >From dba_tables
> Where blkdiff > 100;
>
> To determine reorganization need.
>
> What sql statement is used by others?
>
> Jolene
>
> -----Original Message-----
> Sent: Wednesday, January 07, 2004 2:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I usually recommend Gospel by Jonathan for its completeness
> and a wide range of subjects. The book you mentioned is great
> for beginner as well. As for the number 42, I'll continue using
> it until this Saturday (1/10/2004) when it will become 43. Inflation is
> not as big as you think.
> PS:
> ---
> I was born on 1/10/1961, and that makes January 10th so special. I don't
> have to work on that great day, mostly because it's Saturday.
>
>
> On 01/07/2004 03:09:53 PM, "Thater, William" wrote:
> > Mladen Gogala scribbled on the wall in glitter crayon:
> >
> > > Lemme guess: you just started on your new job as a DBA? You are
> > > another person to which can only wholeheartedly recommend Jonathan's
>
> > > book. As for your questions, the answer is "42".
> >
> > actually, if she's just starting out, i'd recommend Marlene, Rachel
> > and Jim's book first, then Jonathan's.
> >
> > and are you sure it's not "57" now due to inflation?
> >
> > --
> > Bill "Shrek" Thater ORACLE DBA
> > "I'm going to work my ticket if I can..." -- Gilwell song
> > william.thater_at_carrier.utc.com
> > ----------------------------------------------------------------------
> > --
> > Perfection of means and confusion of ends seem to characterize our
> age. -
> > Albert Einstein
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Thater, William
> > INET: William.Thater_at_carrier.utc.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).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Shrake, Jolene
> INET: JEShrake_at_Pella.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).