Oracle-L: RE: ORA-1653: unable to extend table

From: Richard Ji <richard.ji_at_mobilespring.com>
Date: Fri, 29 Nov 2002 11:04:35 -0800
Message-ID: <F001.0050F1F3.20021129110435@fatcity.com>

How are the inserts being done? Are you doing an insert with append hint?

-----Original Message-----
Sent: Friday, November 29, 2002 12:59 PM 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: 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
---------------------------------------------------------------------

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