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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
