Re: Table not reusing deleted space

Sinardy,

I'm not sure I would perform the export/import. It may temporarily mask the problem, but it will return. I think the real issue is that the table has a very large average row length (4898). If you look at the chain count (roughly 1% of the rows), this indicates that the updates are such that they cannot fit into the original block. If you exp/imp, I think you will find that the allocation problem returns rather quickly. One possible solution that has been mentioned in previous posts is to increase the freelists. The intent is to enable inserts to find a block with sufficient free space. You also need to look at the pctfree/pctused and see if they can be set to enable reuse of the blocks. There is also an undocumented parameter (contact OWS) that increases the number of freelist blocks that a transaction will walk before using a new block/allocating a new extent.

Daniel

Sinardy Xing wrote:

> Hi Leng,
>
> I have suggestion, "FOR YOU TO TEST ONLY"
>
> *************CHECK SIZE**************
> 1. EXPORT target tables with COMPRESS = Y parameter
> 2. DROP target tables
> *************CHECK SIZE**************
> 3. CREATE TABLE (target tables)
> 4. IMPORT target tables
> *************CHECK SIZE**************
>
> Good luck....
> Sinardy
>
> -----Original Message-----
> Sent: 02 October 2003 13:50
> To: Multiple recipients of list ORACLE-L
>
> Here are the stats if you're interested. I can't run dbms_space on the table because it will lock up the application. This table is accessed ALL the time. It grabbed another 100M today! Extent management is LOCAL with UNIFORM SIZE of 100M.
>
> 24th Sep 2003
>
> OWNE SEGMENT_NAME TABLESPACE_NAME KB NEXTKB EXT[MAX] % Inc
> ---- --------------------------------- -------------------- ------------ ------------ -------------------- ----------
> CCA CONNECT_TASK[T] CC_TASK_TABS 3,891,200 102,400 38[2147483645] 0
> CCA CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0
> CCA CONNECT_TASK_CRN[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0
> CCA PK_CONNECT_TASK[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0
>
> OWNE TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS CHAIN_CNT
> ---- ------------------------------ ---------- ----------- ---------- ------------ ----------
> CCA CONNECT_TASK 185583 4898 484189 2210 1445
>
> 2nd Oct 2003
>
> OWNER SEGMENT_NAME TABLESPACE_NAME KB NEXTKB EXT[MAX]
> -------------------- --------------------------------- -------------------- ------------ ------------ --------------------
> CCA CONNECT_TASK[T] CC_TASK_TABS 4,198,400 102,400 41[2147483645]
> CCA CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS 102,400 102,400 1[2147483645]
> CCA CONNECT_TASK_CRN[I] CC_TASK_IDXS 102,400 102,400 1[2147483645]
> CCA PK_CONNECT_TASK[I] CC_TASK_IDXS 102,400 102,400 1[2147483645]
>
> OWNE TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS CHAIN_CNT
> ---- ------------------------------ ---------- ----------- ---------- ------------ ----------
> CCA CONNECT_TASK 184113 4958 512699 12100 1528
>
> --------------------------------------------------------------------------------
> From: "Sinardy Xing" <SinardyXing_at_bkgcomsvc.com>
> Date: Wed, 1 Oct 2003 14:51:40 +0800
> Subject: RE: Table not reusing deleted space
>
> Hi Kaing,
>
> Have you check the degree of fragmentation?
>
> have you check your extent size?
>
> Sinardy
>
> ----------------------------------------------------------
> 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
> ---------------------------------------------------------------------
> 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: Daniel Fink
  INET: Daniel.Fink_at_Sun.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).



text/x-vcard attachment: Card for Daniel Fink