RE: ORA-1653: unable to extend table - Why?

From: Richard Ji <richard.ji_at_mobilespring.com>
Date: Fri, 29 Nov 2002 11:49:21 -0800
Message-ID: <F001.0050F241.20021129114921@fatcity.com>

Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM.

Richard Ji

-----Original Message-----
Sent: Friday, November 29, 2002 2:05 PM
To: Multiple recipients of list ORACLE-L

did u coalesced the tablespaces?

-----Original Message-----
Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L

Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000.

After reanalyzing the table I saw the following stats in DBA_TABLES:

num_freelist_blocks: 2266966
avg_space_freelist_blocks: 3895

Unless I'm misreading this I should have had over 8Gig available for inserts.

We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile).

Why isn't it making use of the existing blocks on the freelist?

Oracle 8.1.7.2
Solaris 2.6
PCTFREE = 10
PCTUSED = 75
Block Size = 4K

Jay Miller
--

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

Author: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.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.com
--

Author: Paulo Gomes
  INET: PGomes_at_Datinfor.pt

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.com
--

Author: Richard Ji
  INET: richard.ji_at_mobilespring.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). Received on Fri Nov 29 2002 - 13:49:21 CST