From: (Ari Kaplan)
Subject: Re: How do I reclaim index space?
Date: 19 Jun 1996 16:56:42 -0500
Organization: InterAccess, Chicago's best Internet Provider (708)-498-3189 writes:

>Our production system is now at the point where we are deleting data
>off of it and are hitting index space limits.  I know lost index space due
>to deletes is not recovered.  Does anyone have a script or an idea how
>to drop and re-create indexes so that the space is reclaimed?  Any ideas
>will be tossed around and probably tried.  Thanks ...

>D. Scott Moyer, Jr.               (preferred for work)
>GPU Service Corporation       (preferred for home)
>Reading, PA

Mr. Moyer,

Do just that - drop and recreate the index. Oracle will automatically
create the index with no spaces from deletion. Be sure to size the index
so that it fits within one extent.

When you say that "lost index space lost due to deletes is not recovered",
the space WILL be re-used, depending on your PCT-USED and PCT-FREE. What
you might be thinking is that once you create an additional extent and
delete records, the index does not "shrink" down an extent, and the space
is not "recovered" to be used by other objects. This is true. To fix
this, drop and re-create the index as well.

Best of luck to you,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> Visit my Web Page:                          <->
<->             email:                    <->

Back to Ari Kaplan's Home Page htm">Back to Ari Kaplan's Home Page