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

Well-known, perhaps....not particularly tasty, though....

Hmmm....There isn't anyone on this list named Stella, is there? ;-) (As in Artois...) ;-)

-Mark

On Thu, 2002-12-05 at 03:39, Mogens Nørgaard wrote:
> 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).
> >
> >
> >
> >
> >
> >
> >
>

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