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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > > 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: [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.net > -- > Author: Sinardy Xing > INET: [EMAIL PROTECTED] > > 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: [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).
begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;;;;;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
