From JayMiller@TDWaterhouse.com Thu, 16 Aug 2001 09:24:55 -0700 From: "Miller, Jay" Date: Thu, 16 Aug 2001 09:24:55 -0700 Subject: RE: Index space not freed when rows deleted? Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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" Sent by: cc: root@fatcity.com 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: JayMiller@TDWaterhouse.com 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: ListGuru@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.com -- Author: INET: tday6@csc.com 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: ListGuru@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.com -- Author: Miller, Jay INET: JayMiller@TDWaterhouse.com 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: ListGuru@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).