Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: table reorganizations

Re: table reorganizations

From: Richard Foote <>
Date: Thu, 08 Jan 2004 04:19:27 -0800
Message-ID: <>

> 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.

Hi Jolene

You already received a number of replies why there are issues with using a general formula as above. IF a table is commonly accessed via a FTS AND, IF sufficient deletes without subsequent re-inserts (permanent table shrinkage, ouch, it's a male thing ;) or IF you've set a shocking PCTUSED which prevents inserts reclaiming deleted space, or IF you've set a shocking PCTFREE with no subsequent row size increase (etc) AND FTS access performance causes notable performance issues, you might have a case for a table re-org. The above conditions are not particularly common (perhaps a table containing future bookings for sleepovers at Michael Jackson's place ? ;) but if they do, consider the clustering factor of your most significant index access while you're at it, assuming there is one.

The point I'll like to make are a couple of issues with your formula above.

Firstly, it doesn't consider general block overhead details which means for largish tables with a sum of 100 block or more of overhead, the (rather expensive) re-org would achieve nothing.

Secondly, it doesn't consider blocks above the HWM which could quite easily exceed the 100 mark depending on extent size. Again the re-org would result in a somewhat disappointing outcome.

The formula above will potentially call for the re-org of *all* your larger tables for absolutely no benefit.

Glad you asked the question ....



Please see the official ORACLE-L FAQ:
Author: Richard Foote

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Jan 08 2004 - 06:19:27 CST

Original text of this message