Thanks to you and Kevin!
You'd think I'd have noticed this behavior sometime before, but obviously
not.
Hmm, I'm also thinking now about my partioned tables in the datawarehouse,
some of which have a global index on them. After exporting and dropping an
older partition, I assume all that space is still claimed.
That would explain why when I rebuilt the indexes recently I regained a huge
amount of space.
Always glad to learn something new :).
Jay Miller
-----Original Message-----
Sent: Wednesday, August 15, 2001 5:42 PM
To: Multiple recipients of list ORACLE-L
Index blocks remain when all the leaves are gone. It's late and I'm late
for going home but basically, you deleted rows from the table. The index
removes those pointers but does not otherwise reorganize itself. After you
do a major DML on a table, rebuild the index.
"Miller, Jay"
<JayMiller@TDWater To: Multiple recipients of
list ORACLE-L
house.com> <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Index space not
freed when rows
deleted?
08/15/2001 04:48
PM
Please respond to
ORACLE-L
I'm puzzled.
We had a table that hit maximum extents on our QA server (unlike production
it isn't cleaned out on a daily basis). I deleted most of the rows,
leaving
only a few thousand.
The next day, when inserting more rows I hit the maxextents exceeded error
on an index on that table (of course the number of extents hadn't been
reduced but I would have thought there'd be plenty of room in 121 existing
extents after most of the rows were deleted). I rebuilt the index and it
went from 121 extents to 1 extent, resolving the problem, but leaving some
curiosity.
It's a primary key sequence based index. Any ideas?
Jay Miller
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
