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

Or maybe Miller. It is, after all, the name of a well-known alcoholic beverage. Good thing my first name is not spelled Moans. I would probably not get many emails through spam filters.

Mogens

Miller, Jay wrote:

>Kirti,
>
>Thanks for suggesting the Note, I'm reading it now. I tried replying to you
>directly but my thank you was blocked by your company's spam filter. I'm
>really curious to know what key word flagged it as spam. Oracle?
>
>
>Jay
>
>
>
>-----Original Message-----
>Sent: Wednesday, December 04, 2002 12:49 PM
>To: Multiple recipients of list ORACLE-L
>
>
>freelist groups is 1
>
>-----Original Message-----
>Sent: Tuesday, December 03, 2002 9:59 PM
>To: Multiple recipients of list ORACLE-L
>
>
>What is the FREELIST GROUPS for the table?
>
>Waleed
>
>-----Original Message-----
>Sent: Tuesday, December 03, 2002 3: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).