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).
Received on Fri Nov 29 2002 - 17:23:48 CST
