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: Fragmentation & Locally Managed Tablespaces

Re: Fragmentation & Locally Managed Tablespaces

From: <MHately_at_etech-uk.com>
Date: Thu, 28 Jun 2001 04:30:42 -0700
Message-ID: <F001.0033B8C2.20010628043535@fatcity.com>

Rachel,
That maximum isn't a hard limit. Oracle themselves impose no limit of this kind. After you use all of the available space in the segment header block, any additional space map entries are overflowed into additional extent maps within the segment.
This means that because the used extent information isn't cached in the data dictionary, queries against dba_extents etc. can cause many blocks to be read from LMTs if the map entries have overflowed in this manner.

Therefore, the limitation is more of a recommendation to ensure that good performance is maintained.

Regards,
Mike

|--------+----------------------->

| | "Rachel |
| | Carmichael" |
| | <carmichr_at_hot|
| | mail.com> |
| | |
| | 06/28/01 |
| | 10:46 AM |
| | Please |
| | respond to |
| | ORACLE-L |
| | |
|--------+-----------------------> >-------------------------------------------------------------------| | | | To: Multiple recipients of list ORACLE-L | | <ORACLE-L_at_fatcity.com> | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Fragmentation & Locally Managed Tablespaces| >-------------------------------------------------------------------|

so we are going back to the Oracle specified maximum extents for a particular blocksize?

>From: Paul Drake <paled_at_home.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Fragmentation & Locally Managed Tablespaces
>Date: Thu, 28 Jun 2001 00:15:50 -0800
>
>VIVEK_SHARMA wrote:
> >
> > Is Fragmentation of Objects meaningless in Locally managed Tablespaces
> > Assuming EXTENTS is 3,000 for Some of the Objects
> >
> > Or Do the Objects need to be DE-Fragmented using exp/imp ?
> >
>
>Vivek,
>
>funny, I was just looking into this tonight.
>
>what is your block size?
>
>I would recommend a quick visit to our friend Steve Adams site:
>
>http://www.ixora.com.au/tips/creation/extents.htm
>
>"For this reason, we recommend that the number of extents per segment in
>locally managed tablespaces be limited to the number of rows that can be
>accommodated in the extent map within the segment header block
>- that is, approximately (db_block_size / 16) - 7."
>
>for an 8 KB block size - that is 505 extents per segment.
>
>Those objects would be good candidates to move to a different (new?)
>tablespace with a larger (uniform) extent size.
>
>Paul
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Paul Drake
>   INET: paled_at_home.com
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California        -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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: 
  INET: MHately_at_etech-uk.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Thu Jun 28 2001 - 06:30:42 CDT

Original text of this message

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