Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-1653: unable to extend table - Why?

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

From: Jeremy Pulcifer <Jeremy.Pulcifer_at_kadiri.com>
Date: Tue, 03 Dec 2002 14:46:13 -0800
Message-ID: <F001.005117F9.20021203144613@fatcity.com>


It just depends on what your definition of "it" is...

> -----Original Message-----
> From: Miller, Jay [mailto:JayMiller_at_TDWaterhouse.com]
> Sent: Tuesday, December 03, 2002 12:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ORA-1653: unable to extend table - Why?
>
>
> 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: Ron Thomas
> INET: rthomas_at_hypercom.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: 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: Jeremy Pulcifer
  INET: Jeremy.Pulcifer_at_kadiri.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).
Received on Tue Dec 03 2002 - 16:46:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US