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

Hmm...not sure if this is what's biting you, but if you're out of ITL slots on the block, Oracle will move to the next block on the free list. If all the blocks on the free list have filled ITLs, Oracle will add an extent to the free list. I don't suppose you have MAXTRANS set to 1 or some other very low number? If so, and with concurrent inserts happening, it's possible that you could be allocating more blocks to the free list, even if there are tons of blocks already on the free list, simply due to the ITL shortage. (Note that this is why you'll never see mode 4 TX enqueue waits on insert to a table.)

If MAXTRANS isn't really low, and if you're not doing direct-load (APPEND hint) inserts, then I'm stumped.

-Mark

On Tue, 2002-12-03 at 17:09, Fink, Dan wrote:
> Hmm...Why didn't they ask for your buffer cache hit ratio?
>
>
> Seriously, I've pondered this and it comes down to a question. What would
> cause a transaction not to use blocks on the freelist? If a transaction
> cannot use these blocks, then it must allocate new space. If the transaction
> is set up so as to allocate space above the HWM, we have the same scenario.
> Are there transactions that are allocating blocks off the freelist? How deep
> will a transaction read the freelist to find an open block before giving up
> and allocating space? Are the inserts of such size that they would not fit
> into the space in the blocks on the freelist?
>
> I don't know the answers, but it seems that the questions may offer some
> clues. I can't wait to find out the real answer!
>
> -----Original Message-----
> Sent: Tuesday, December 03, 2002 1:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Just for grins, here's the level of support I'm getting on my Oracle TAR:
>
> ----------------------------------
> You had stated earlier:
> 1/ 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.
>
> 2/ I've had to add another data file and it has already grown to 600 Meg.
>
> If the table is only 600 mb, then there is no way that it can have 8 gb of
> free space. Since you have a lot of blocks with some free space, you may
> want to export and import the table back to re-org the table...
> ----------------------------------
>
> Someone should inform these people that a table can consist of more than one
> datafile...
>
>
> -----Original Message-----
> Sent: Tuesday, December 03, 2002 11:54 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I had one thought.
> The Freelist parameter for this table is only set to 1. Is it possible that
> if it gets tied up with contention for the freelist that it grabs a new
> extent?
>
> I see that some of these blocks are being written to, the
> num_freelist_blocks is now down to 2095705. But the new data file has grown
> to 600 Meg.
>
> I've opened a TAR to see what Oracle says but I'm not encouraged by the
> first question they sent me (which was asking to query dba_free_space).
>
> Jay
>
> -----Original Message-----
> Sent: Monday, December 02, 2002 6:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> One thing I haven't seen mentioned yet is what degree of parallelism is
> defined for the table?
> What is the next extent size set to?
> If the table is paralleled, EACH parallel worker will grab a next extent
> sized segment. (Been bit by
> this a few times...)
> How many indexes and are they in the same tablespace?
>
> Ron Thomas
> Hypercom, Inc
> rthomas_at_hypercom.com
> Each new user of a new system uncovers a new class of bugs. -- Kernighan
>
>
>
>
> JayMiller_at_TDWater
>
> house.com To:
> ORACLE-L_at_fatcity.com
>
> Sent by: cc:
>
> root_at_fatcity.com Subject: RE: ORA-1653:
> unable to extend table - Why?
>
>
>
>
>
> 12/02/2002 02:04
>
> PM
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Yep, I agree that coalescing is irrelevant in my current situation. In any
> event there was no free space until I added the additional datafile but
> there was the 8gig of space on the freelists.
>
> Jay
>
> -----Original Message-----
> Sent: Friday, November 29, 2002 8:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Richard,
>
> if pctincrease is zero, and there are a large number of contiguous
> smaller extents, SMON will not automatically coalesce the tablespace.
> However, whether or not SMON does an automatic coalesce, if you need an
> extent that is larger than any of the small ones, Oracle will coalesce
> those smaller extents to make the one you need. so Jay would not have
> needed to add a datafile no matter what, if he was not doing a direct
> path insert.
>
> As for meeting in person.... there is a user group meeting on Dec 12
> (check www.nyoug.org for details). You can meet me, and more
> importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita
> Bardeen, also of this list. They are all presenting :)
>
> I saw Priscilla about a month ago, haven't talked with her since.
>
> Rachel
>
> --- Richard Ji <richard.ji_at_mobilespring.com> wrote:
> > Rachel,
> >
> > What I mean to say is when there are a lot of contiguous smaller free
> > extents.
> > Then coalesce will produce a larger free extent so Jay wouldn't have
> > to
> > add a datafile for his table to grow.
> >
> > On the automatically coalescing part, I believe SMON will only
> > coalesce
> > when pctincrease != 0, or has that changed? My understand could be
> > outdated.
> > With LMT one doesn't have to worry about it.
> >
> > Have a Happy Thanksgiving.
> >
> > PS, I am in New York too, would love to meet you in person some time.
> > Have
> > you
> > talked to Priscilla lately?
> >
> > Richard Ji
> >
> >
> > -----Original Message-----
> > Sent: Friday, November 29, 2002 5:29 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > how would coalescing help even if there were a lot of smaller free
> > extents? Oracle would do the coalesce automatically, there would be
> > no
> > difference between manually coalescing or allowing Oracle to do it
> > when
> > a new extent was needed.
> >
> >
> > --- Richard Ji <richard.ji_at_mobilespring.com> wrote:
> > > 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).
> > >
> >
> >
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > http://mailplus.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.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
> > ---------------------------------------------------------------------
> > 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).
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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: 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).
>
>
>
>
>

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it
well."
 						-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: mark_at_bobak.net

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