Oracle-L: RE: Table not reusing deleted space

From: Sinardy Xing <SinardyXing_at_bkgcomsvc.com>
Date: Tue, 30 Sep 2003 23:49:28 -0800
Message-ID: <F001.005D18CA.20030930234928@fatcity.com>

Hi Kaing,

Have you check the degree of fragmentation?

have you check your extent size?

Sinardy

-----Original Message-----
Sent: 01 October 2003 14:20
To: Multiple recipients of list ORACLE-L

Hello everyone,

Env: 8.1.7.4, SunOs 5.8 64 Bit

We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows.

We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space.

In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem?

Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot.

TIA, Leng.

Leng Kaing
Email: leng.kaing_at_team.telstra.com
Phone: +61-3-9203-7589
Mobile: +61-417-371-348

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Kaing, Leng
INET: Leng.Kaing_at_team.telstra.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_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.net
--

Author: Sinardy Xing
INET: SinardyXing_at_bkgcomsvc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------