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

I always overlook things, so I'll just ask and hope it isn't already on the thread: Exactly what kind of activity is going on against this wonderful table? Is this table placed in an LMT? If yes, is it a Uniform LMT? Could you trace the processes/users doing stuff to the table so that we could see if there's contention for ITL slots or other such things?

Mogens

Mark J. Bobak wrote:

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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