Dictionary Managed and auto-coalesce -- was RE: locally

In a Dictionary-Managed tablespace [the "good old days" that Rachel is talking about],
SMON would auto-coalesce free extents in the background if PCTINCREASE was > 0. However, even where PCTINCREASE=0, a CREATE TABLE would still coalesce contigous extents. Of course, if the CREATE statement could find a Free Extent equal to or larger than the required size, it would use such extent [breaking up the larger into a used and a free extent]. The CREATE would coalesce only if it couldn't find an equal or larger extent.

Hemant

At 12:48 PM 16-04-03 -0800, you wrote:
>IIRC Oracle would only coalesce automatically if PCTIncrease was
>non-zero for the tablespace, hence one should always set it to 1% and
>... Hang on a minute I seem to have entered the same time warp as you.
>
>Niall
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of
> > Rachel Carmichael
> > Sent: 16 April 2003 17:34
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: locally managed tbs in 9i and extent sizes
> >
> >
> > Richard,
> >
> > But Oracle would coalesce on the fly, as necessary.
> >
> > I forget the order of the first two (it's been awhile!) but
> > as I recall, Oracle would look for:
> >
> > 1) an extent of exactly the same size as required
> > 2) an extent larger than that required
> > 3) "contiguous" extents that had not yet been coalesced which
> > made up the correct size (or larger)
> >
> > yeah, the good old days, when DBAs carved databases out of
> > stone, and buffer cache hit ratios meant something and ....
> > oh wait, what am I thinking!
> >
> > Rachel
> > --- Richard Foote <richard.foote_at_bigpond.com> wrote:
> > > Hi Rachel,
> > >
> > > But until the "contiguous" extents get coalesced, they
> > would be not be
> > > treated as contiguous by Oracle.
> > >
> > > They indeed were the days... ;)
> > >
> > > Cheers
> > >
> > > Richard
> > >
> > >
> > > > > more than the size of required extent. To know the largest
> > > available
> > > > > contiguous chunk of space, use
> > > > > ( select max(bytes) from dba_free_space where
> > > > > tablespace_name='<tablespace name>'; )
> > > >
> > > >
> > > > Actually that's not quite true, because it's possible for
> > > contiguous
> > > > extents to be listed in dba_free_space as two separate entries,
> > > > depending on when they were freed up and if they had been
> > allocated
> > > at
> > > > different times.
> > > >
> > > > you'd need to see if there is an entry where block_id = prior
> > > > block_id+blocks
> > > >
> > > > back in the days before I used uniform extents, I had a
> > script that
> > > > generated a report of contiguous space by tablespce. I don't need
> > > it
> > > > anymore, LMTs and uniform sizing has allowed me to stop worrying
> > > about
> > > > having contiguous space available.
> > > >
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Richard Foote
> > > INET: richard.foote_at_bigpond.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!?
> > The New Yahoo! Search - Faster. Easier. Bingo http://search.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > 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.net
>--
>Author: Niall Litchfield
> INET: niall.litchfield_at_dial.pipex.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).

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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