From: akaplan@interaccess.com (Ari Kaplan)
Newsgroups: comp.databases.oracle
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
dmoyer@gpu.com 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. dmoyer@gpu.com (preferred for work)
>GPU Service Corporation dsmoyer@enter.net (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: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page