RE: fragmentation

[Rachel Carmichael]

Index tablespace has the most wasted space and yes, there are several
indices in those tablespaces. I need to keep it available or I'd just
drop and recreate the entire index.

good to know I wasn't hallucinating!


--- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote:
> Rachel - Right you are, as "Stop Defragmenting . . . " points out,
> there are
> several types of fragmentation. 
>- Is it your table or your index that contains wasted space?
>- Are there multiple objects in each tablespace or just a single
> object?
>- Are you trying to keep the index available to users while you're
> rebuilding? I'm assuming this is the reason you are looking at
> rebuilding
> the index twice. Or is it because rebuilding an index probably won't
> cause a
> large sort?
> Overall it looks like a reasonable plan, though I haven't worked much
> with
> partitioned indexes myself.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Thursday, August 14, 2003 12:29 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I never thought I would care about fragmentation again, especially
> once
> I built all my databases using LMTs. 
> 
> But I've actually found a reason to care, sort of. We have a data
> warehouse with lots of "wasted" space in it. By that I mean, many of
> the partitions (we partition by month) are way larger than is needed
> for the data contained within them. Once all the data for a month is
> loaded, that's it, it doesn't grow anymore.
> 
> The oversized tablespaces are those associated with the indexes. Part
> of the problem is that the hosting company we use has a threshhold of
> 80% and when a tablespace is 80% full, they automatically expand the
> datafiles. part of the problem is that for a time there were problems
> with the loads and we had to delete/re-insert/delete/re-insert data. 
> 
> I want to shrink the datafiles, but they are "fragmented". yes, I
> know
> "disk is cheap", but having gone through a 3 month exercise in
> frustration trying to get the data center management to spend a few
> thousand dollars on more memory so that  we could actually run
> reports,
> I'm not going there.
> 
> I was planning on doing the following, just wanted a sanity check
> from
> the list:
> 
> 1) create a very large holding tablespace, to use as a rebuilding
> area
> 
> then, on a partition by partition basis:
> 
> a) rebuild the index partition into the holding tablespace (lots of
> indexes to rebuild in each partition)
> b) rebuild the index partition back into the original tablespace (my
> hope is that this will effectively "compress" the index extents)
> c) shrink the index partition datafiles
> 
> 
> Does this make sense or am I overtired and not thinking? Is there a
> better/faster/EASIER way to do what I wanted to do?
> 
> Rachel
> 
> 
> Rachel
> 
> 
> 
> 
> 
> 
> 
> __
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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.net
> -- 
> Author: DENNIS WILLIAMS
>   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! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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

Re: fragmentation

[Tanel Poder]

Hi!

>- Are you trying to keep the index available to users while you're
> rebuilding? I'm assuming this is the reason you are looking at rebuilding
> the index twice. Or is it because rebuilding an index probably won't cause
a
> large sort?

Sort is still needed, even when rebuilding. It's just that less data is
required to read when rebuilding from index than building from table.

Rachel, you probably already planned using nologging and possibly parallel
clause + setting sort_area_size and maybe db_file_multiblock_read_count,
depending on your extent size.
I would recommend you to rebuild one index partition as you planned and then
build other, similarly sized partition from scratch.
Then measure the time & IO difference.
When building from scratch, you have to read lots of data from table, sort
it (to temp), then copy it back from temp.
For double rebuilding, you will have to read lesser amount of data, sort it
to temp, copy it to your big tablespace. Then read it again, sort it to temp
and copy it to your original database again. Of course, verify that there's
no other extents in your original tablespace, otherwise you might not be
able to resize your files smaller.

Also, depending on your IO layout, if you got any spare disks for temporary
use, you could make additional temp tablespace on them, set the index
recreating user's temp ts to that one to avoid disk contention..

Tanel.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  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).

RE: fragmentation

[DENNIS WILLIAMS]

Rachel - Right you are, as "Stop Defragmenting . . . " points out, there are
several types of fragmentation. 
   - Is it your table or your index that contains wasted space?
   - Are there multiple objects in each tablespace or just a single object?
   - Are you trying to keep the index available to users while you're
rebuilding? I'm assuming this is the reason you are looking at rebuilding
the index twice. Or is it because rebuilding an index probably won't cause a
large sort?
Overall it looks like a reasonable plan, though I haven't worked much with
partitioned indexes myself.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Thursday, August 14, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


I never thought I would care about fragmentation again, especially once
I built all my databases using LMTs. 

But I've actually found a reason to care, sort of. We have a data
warehouse with lots of "wasted" space in it. By that I mean, many of
the partitions (we partition by month) are way larger than is needed
for the data contained within them. Once all the data for a month is
loaded, that's it, it doesn't grow anymore.

The oversized tablespaces are those associated with the indexes. Part
of the problem is that the hosting company we use has a threshhold of
80% and when a tablespace is 80% full, they automatically expand the
datafiles. part of the problem is that for a time there were problems
with the loads and we had to delete/re-insert/delete/re-insert data. 

I want to shrink the datafiles, but they are "fragmented". yes, I know
"disk is cheap", but having gone through a 3 month exercise in
frustration trying to get the data center management to spend a few
thousand dollars on more memory so that  we could actually run reports,
I'm not going there.

I was planning on doing the following, just wanted a sanity check from
the list:

1) create a very large holding tablespace, to use as a rebuilding area

then, on a partition by partition basis:

a) rebuild the index partition into the holding tablespace (lots of
indexes to rebuild in each partition)
b) rebuild the index partition back into the original tablespace (my
hope is that this will effectively "compress" the index extents)
c) shrink the index partition datafiles


Does this make sense or am I overtired and not thinking? Is there a
better/faster/EASIER way to do what I wanted to do?

Rachel


Rachel







__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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.net
-- 
Author: DENNIS WILLIAMS
  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).

RE: Fragmentation ?

[John Kanagaraj]

Aaah - now I understand. The LRU rule does not hold good once the Goddess
applies her personal touch and 'accesses' these blocks (sorry - books) :)
So they need to stay in the DB B(l)ock buffer cache as they now migrate to
the MRU end of the cache chain... The blocks that do need to go out of the
(book) cache are actually those that have been updated!

Couldn't resist the rambling - it is Friday! 

John

> -Original Message-
> From: Rachel Carmichael [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 13, 2003 12:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Fragmentation ?
> 
> 
> your wife's rule wouldn't work in my case... every few years (usually
> less than 10 but on occasion 10 works too) I go on a "re-reading
> spree". back to old friends, "comfort food" of books.
> 
> I'd have to buy all new copies if I threw books out.
> 
> I do, on rare occasion, get rid of books. My oracle books that tell me
> how to tune Version 7 are one example :)
> 
> --- Niall Litchfield <[EMAIL PROTECTED]> wrote:
> > I worked with a really smart guy once whom I won't name for obvious
> > reasons. He had previously worked for a software co that said "Our
> > product includes an archive routine". It didn't, they never had to
> > write
> > one because hey disks held 3 times the storage for half the price
> > before
> > anyone wanted to archive anything - at which point you just bought
> > some
> > more storage. 
> > 
> > I also probably ought to include the ongoing marital dispute that I
> > am
> > having regarding books, my wife maintains that anything I haven't
> > accessed for a decade could be disposed of (think Tolkien, 
> Donaldson,
> > Asimov, Shakespeare, Auden).  *I* maintain "well we could always buy
> > another bookcase". Logic tends to dictate my wifes approach,
> > management
> > I feel confident would say "ah well doesn't cost much lets buy
> > another
> > bookcase". 
> > 
> > In summary Niall's 2nd rule states that "data always goes in but
> > never
> > comes out". It's parkinsons law for databases
> > 
> > Niall
> > 
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> > > Behalf Of Stephen Lee
> > > Sent: 13 June 2003 18:45
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Fragmentation ?
> > > 
> > > 
> > > 
> > > That's one thing good about the databases here.  Tablespace 
> > > fragmentation is rarely a problem.  Most of the database here 
> > > are a Database Roach Motel: "Data checks in.  It doesn't 
> > > check out."  Somehow, the data purge part of the application 
> > > -- that they intended to put in "one of these days" -- never 
> > > got written.
> > > 
> > > 
> > > 
> > > (For non-USA dwellers, Roach Motel is a trap for roaches.  It 
> > > has a sticky floor, and the sales motto is "Roaches check in. 
> > > They don't check out.")
> > > -- 
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > -- 
> > > Author: Stephen Lee
> > >   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.net
> -- 
> Author: Niall Litchfield
>   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 sen

RE: Fragmentation ?

[Rachel Carmichael]

your wife's rule wouldn't work in my case... every few years (usually
less than 10 but on occasion 10 works too) I go on a "re-reading
spree". back to old friends, "comfort food" of books.

I'd have to buy all new copies if I threw books out.

I do, on rare occasion, get rid of books. My oracle books that tell me
how to tune Version 7 are one example :)

--- Niall Litchfield <[EMAIL PROTECTED]> wrote:
> I worked with a really smart guy once whom I won't name for obvious
> reasons. He had previously worked for a software co that said "Our
> product includes an archive routine". It didn't, they never had to
> write
> one because hey disks held 3 times the storage for half the price
> before
> anyone wanted to archive anything - at which point you just bought
> some
> more storage. 
> 
> I also probably ought to include the ongoing marital dispute that I
> am
> having regarding books, my wife maintains that anything I haven't
> accessed for a decade could be disposed of (think Tolkien, Donaldson,
> Asimov, Shakespeare, Auden).  *I* maintain "well we could always buy
> another bookcase". Logic tends to dictate my wifes approach,
> management
> I feel confident would say "ah well doesn't cost much lets buy
> another
> bookcase". 
> 
> In summary Niall's 2nd rule states that "data always goes in but
> never
> comes out". It's parkinsons law for databases
> 
> Niall
> 
> > -----Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> > Behalf Of Stephen Lee
> > Sent: 13 June 2003 18:45
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Fragmentation ?
> > 
> > 
> > 
> > That's one thing good about the databases here.  Tablespace 
> > fragmentation is rarely a problem.  Most of the database here 
> > are a Database Roach Motel: "Data checks in.  It doesn't 
> > check out."  Somehow, the data purge part of the application 
> > -- that they intended to put in "one of these days" -- never 
> > got written.
> > 
> > 
> > 
> > (For non-USA dwellers, Roach Motel is a trap for roaches.  It 
> > has a sticky floor, and the sales motto is "Roaches check in. 
> > They don't check out.")
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Stephen Lee
> >   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.net
> -- 
> Author: Niall Litchfield
>   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! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

RE: Fragmentation ?

[Niall Litchfield]

I worked with a really smart guy once whom I won't name for obvious
reasons. He had previously worked for a software co that said "Our
product includes an archive routine". It didn't, they never had to write
one because hey disks held 3 times the storage for half the price before
anyone wanted to archive anything - at which point you just bought some
more storage. 

I also probably ought to include the ongoing marital dispute that I am
having regarding books, my wife maintains that anything I haven't
accessed for a decade could be disposed of (think Tolkien, Donaldson,
Asimov, Shakespeare, Auden).  *I* maintain "well we could always buy
another bookcase". Logic tends to dictate my wifes approach, management
I feel confident would say "ah well doesn't cost much lets buy another
bookcase". 

In summary Niall's 2nd rule states that "data always goes in but never
comes out". It's parkinsons law for databases

Niall

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> Behalf Of Stephen Lee
> Sent: 13 June 2003 18:45
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Fragmentation ?
> 
> 
> 
> That's one thing good about the databases here.  Tablespace 
> fragmentation is rarely a problem.  Most of the database here 
> are a Database Roach Motel: "Data checks in.  It doesn't 
> check out."  Somehow, the data purge part of the application 
> -- that they intended to put in "one of these days" -- never 
> got written.
> 
> 
> 
> (For non-USA dwellers, Roach Motel is a trap for roaches.  It 
> has a sticky floor, and the sales motto is "Roaches check in. 
> They don't check out.")
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Stephen Lee
>   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.net
-- 
Author: Niall Litchfield
  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).

RE: Fragmentation ?

[Seefelt, Beth]

I think those guys work here now  :-)

-Original Message-
Sent: Friday, June 13, 2003 1:45 PM
To: Multiple recipients of list ORACLE-L



That's one thing good about the databases here.  Tablespace
fragmentation is
rarely a problem.  Most of the database here are a Database Roach Motel:
"Data checks in.  It doesn't check out."  Somehow, the data purge part
of
the application -- that they intended to put in "one of these days" --
never
got written.



(For non-USA dwellers, Roach Motel is a trap for roaches.  It has a
sticky
floor, and the sales motto is "Roaches check in. They don't check out.")
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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.net
-- 
Author: Seefelt, Beth
  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).

RE: Fragmentation ?

[Stephen Lee]

That's one thing good about the databases here.  Tablespace fragmentation is
rarely a problem.  Most of the database here are a Database Roach Motel:
"Data checks in.  It doesn't check out."  Somehow, the data purge part of
the application -- that they intended to put in "one of these days" -- never
got written.



(For non-USA dwellers, Roach Motel is a trap for roaches.  It has a sticky
floor, and the sales motto is "Roaches check in. They don't check out.")
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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).

RE: RE: Fragmentation ?

[DENNIS WILLIAMS]

Well said. For people that can only comprehend a simple solution, it is much
more comfortable to have a single answer. The old "reorganize to a single
extent" was always easy to understand. Along the way as a side-effect it
cured other types of fragmentation, but if the underlying causes of the
fragmentation are understood, then fewer reorganizations would be needed.
   The paper "Stop Defragmenting . . ." isn't one of those you can skim and
then set aside. It needs to be intensively studied. LMT isn't completely
foolproof, so you need to understand the underlying premises. As to the
types of fragmentation, read the introduction. It explains which chapter
discusses which type of fragmentation.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, June 13, 2003 9:15 AM
To: Multiple recipients of list ORACLE-L


there was a debate on here 2 weeks ago where it was concluded that until you
get to thousands of extents it just doesnt matter how many you have. 
> 
> From: "VIVEK_SHARMA" <[EMAIL PROTECTED]>
> Date: 2003/06/13 Fri AM 06:39:36 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Fragmentation ?
> 
> Dennis , List 
> 
> What may be the OTHER forms of fragmentation ?
> 
> What Number of Extents may be considered Critical warranting
RE-Organization for Manually Sized Objects existing in LMTs ? 
> 
> Thanks for the great paper . Had read it previously though .
> 
> Thanks
> 
> 
> -Original Message-
> Sent: Wednesday, June 11, 2003 8:25 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Vivek
>Make sure you've read "How to Stop Defragmenting and Start Living" 
> http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
> The authors point out that uniform extents stop fragmentation at the
> tablespace level. However they point out that there are other forms of
> fragmentation.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Wednesday, June 11, 2003 9:15 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE =
> "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ?
> 
> With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT
be
> Manually defined in the Table Creation Script )> , 
> which is allowed when having allocation_type="USER" . 
> 
> Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to
be Created 
> in the SAME LOCALLY managed Tablespace & thus reduces Total Number of
Extents for
> the respective Table. Our Application does have Objects of Dissimilar
Sizes
> Existing tin the Same Tablespace .
> 
> Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation
> Irrespective of the Number of Extents of the Object (in a Locally Managed
> Tablespace) ? Does it further imply NO further need to Look at Number of
> Extents of an Object in a Locally Managed Tablespace ?
> 
> NOTE Allocation_type can be made = "USER" by using the stored procedures
:- 
> dbms_space_admin.tablespace_migrate_from_local /
> dbms_space_admin.tablespace_migrate_to_local
> 
> Am i still Lost in the World of Oracle 7 ?
> 
> Thanks
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: VIVEK_SHARMA
>   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.net
> -- 
> Author: DENNIS WILLIAMS
>   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

RE: RE: Fragmentation ?

[Jamadagni, Rajendra]

Title: RE: RE: Fragmentation ?





Depends ... who you ask ...


If you ask Microsoft
  1. you are fragmented if you have at-least _one_ non-windows server in your corporation
  2. Your thinking is fragmented if you are even _considering_ LINUX


If you ask SCO
  1. You are fragmented if you use AIX
  2. You are fragmented if you read every line of GPL 


If you ask Oracle Experts
  1. Some will say "Do you have a problem? if none, don't worry"
  2. Some will say "More than x extents is bad" but X varies from 2 to 1024 to 4096
  3. Some will advise use LMT with Uniform extents and live happily there after.


I could think of some political ones, but this is a technical list ...
TGIF
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


> 
> From: "VIVEK_SHARMA" <[EMAIL PROTECTED]>
> Date: 2003/06/13 Fri AM 06:39:36 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Fragmentation ?
> 
> Dennis , List 
> What may be the OTHER forms of fragmentation ?
> What Number of Extents may be considered Critical warranting RE-Organization for Manually Sized Objects existing in LMTs ? 

> Thanks for the great paper . Had read it previously though .
> Thanks
> 



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: RE: Fragmentation ?

[rgaffuri]

there was a debate on here 2 weeks ago where it was concluded that until you get to 
thousands of extents it just doesnt matter how many you have. 
> 
> From: "VIVEK_SHARMA" <[EMAIL PROTECTED]>
> Date: 2003/06/13 Fri AM 06:39:36 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Fragmentation ?
> 
> Dennis , List 
> 
> What may be the OTHER forms of fragmentation ?
> 
> What Number of Extents may be considered Critical warranting RE-Organization for 
> Manually Sized Objects existing in LMTs ? 
> 
> Thanks for the great paper . Had read it previously though .
> 
> Thanks
> 
> 
> -Original Message-
> Sent: Wednesday, June 11, 2003 8:25 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Vivek
>Make sure you've read "How to Stop Defragmenting and Start Living" 
> http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
> The authors point out that uniform extents stop fragmentation at the
> tablespace level. However they point out that there are other forms of
> fragmentation.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Wednesday, June 11, 2003 9:15 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE =
> "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ?
> 
> With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be
> Manually defined in the Table Creation Script )> , 
> which is allowed when having allocation_type="USER" . 
> 
> Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to be Created 
> in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for
> the respective Table. Our Application does have Objects of Dissimilar Sizes
> Existing tin the Same Tablespace .
> 
> Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation
> Irrespective of the Number of Extents of the Object (in a Locally Managed
> Tablespace) ? Does it further imply NO further need to Look at Number of
> Extents of an Object in a Locally Managed Tablespace ?
> 
> NOTE Allocation_type can be made = "USER" by using the stored procedures :- 
> dbms_space_admin.tablespace_migrate_from_local /
> dbms_space_admin.tablespace_migrate_to_local
> 
> Am i still Lost in the World of Oracle 7 ?
> 
> Thanks
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: VIVEK_SHARMA
>   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.net
> -- 
> Author: DENNIS WILLIAMS
>   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.net
> -- 
> Author: VIVEK_SHARMA
>   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.net
-- 
Autho

RE: Fragmentation ?

[VIVEK_SHARMA]

Dennis , List 

What may be the OTHER forms of fragmentation ?

What Number of Extents may be considered Critical warranting RE-Organization for 
Manually Sized Objects existing in LMTs ? 

Thanks for the great paper . Had read it previously though .

Thanks


-Original Message-
Sent: Wednesday, June 11, 2003 8:25 PM
To: Multiple recipients of list ORACLE-L


Vivek
   Make sure you've read "How to Stop Defragmenting and Start Living" 
http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
The authors point out that uniform extents stop fragmentation at the
tablespace level. However they point out that there are other forms of
fragmentation.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, June 11, 2003 9:15 AM
To: Multiple recipients of list ORACLE-L



Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE =
"UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ?

With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be
Manually defined in the Table Creation Script )> , 
which is allowed when having allocation_type="USER" . 

Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to be Created 
in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for
the respective Table. Our Application does have Objects of Dissimilar Sizes
Existing tin the Same Tablespace .

Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation
Irrespective of the Number of Extents of the Object (in a Locally Managed
Tablespace) ? Does it further imply NO further need to Look at Number of
Extents of an Object in a Locally Managed Tablespace ?

NOTE Allocation_type can be made = "USER" by using the stored procedures :- 
dbms_space_admin.tablespace_migrate_from_local /
dbms_space_admin.tablespace_migrate_to_local

Am i still Lost in the World of Oracle 7 ?

Thanks



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  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.net
-- 
Author: DENNIS WILLIAMS
  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.net
-- 
Author: VIVEK_SHARMA
  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).

RE: Fragmentation ?

[DENNIS WILLIAMS]

Vivek
   Make sure you've read "How to Stop Defragmenting and Start Living" 
http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
The authors point out that uniform extents stop fragmentation at the
tablespace level. However they point out that there are other forms of
fragmentation.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, June 11, 2003 9:15 AM
To: Multiple recipients of list ORACLE-L



Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE =
"UNIFORM" OVER 
dba_tablespaces.ALLOCATION_TYPE = "USER" ?

With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be
Manually defined 
in the Table Creation Script )> which is allowed when
having allocation_type="USER" . 

This allows Objects with Different NEXT_EXTENT Sizes to be Created in the
SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for
the respective Table. Our Application does have Objects of Dissimilar Sizes
Existing tin the Same Tablespace .

Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation
Irrespective of the Number of Extents of the Object (in a Locally Managed
Tablespace) ? Does it further imply NO further need to Look at Number of
Extents of an Object in a Locally Managed Tablespace ?

NOTE Allocation_type can be made = "USER" by using the stored procedures :- 
dbms_space_admin.tablespace_migrate_from_local /
dbms_space_admin.tablespace_migrate_to_local

Am i still Lost in the World of Oracle 7 ?

Thanks



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  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.net
-- 
Author: DENNIS WILLIAMS
  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).

Re: FRAGMENTATION QUESTION?

[Ron Rogers]

Seema,
 If you COPY the datafiles at the OS level then you are getting an
exact image of the datafile. The same for the RMAN copy command. If you
use SQL "insert ,, select " type of copying data from one server to
another then the data will fill the extents and eliminate fragmentation.
It will not remove unused area in the extents, only carefull  planning
will make it close to full.
ROR mª¿ªm

>>> [EMAIL PROTECTED] 03/06/02 02:12PM >>>
Hi
If one tablespace has 10 tables and 6 are fragmented table in that 
particular tablespace.If I use COPY command to move data from one
server to 
another then is COPy command bring data with fragmented tables data?
Thx
-Seema



_
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Seema Singh
  INET: [EMAIL PROTECTED] 

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: [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: Ron Rogers
  INET: [EMAIL PROTECTED]

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

Re: FRAGMENTATION QUESTION?

[Ora NT DBA]

Hi Seema,

define what you mean when you say 6 are fragmented.

John

[EMAIL PROTECTED] wrote:

> Hi
> If one tablespace has 10 tables and 6 are fragmented table in that 
> particular tablespace.If I use COPY command to move data from one 
> server to another then is COPy command bring data with fragmented 
> tables data?
> Thx
> -Seema
>
>
>
> _
> Get your FREE download of MSN Explorer at 
> http://explorer.msn.com/intl.asp.
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ora NT DBA
  INET: [EMAIL PROTECTED]

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

RE: FRAGMENTATION ISSUE?

[Miller, Jay]

Shaibal is right on target.  If you're going to go to the trouble to
defragment then you may as well switch to uniform extent sizing so you don't
have the problem in the future.
 
If you have the room you can do it in stages.  First create an index
tablespace and rebuild all your indexes in that.  Then create a table
tablespace(s), then you can export all the tables, drop them and recreate
the table structures with uniform extents or locally managed and import
them.
 
If you have tables of widely varying size then you could set up multiple
tablespaces (ts_small, ts_medium, etc).
 
Jay Miller

-Original Message-
Sent: Thursday, February 07, 2002 8:03 PM
To: Multiple recipients of list ORACLE-L



Seema,

>From your posting, it is not clear to me about the content of the mentioned
tablespace. If it contains all of one users schema you can just take export
for that particular schema. The step when you are recreating the tablespace,
create it with pct increase 0 and do create initial and next extents same
size or you can create the tablespace as a locally managed tablespace - this
is where your fragmentation is going to be stopped. The rest looks fine to
me.

Slightly off topic, it is not good practice to create everything belong to a
owner in the same tablespace. For sure tables and indexes should have their
seperate tablespaces. 

Shaibal



>From: "Seema Singh" 
>Reply-To: [EMAIL PROTECTED] 
>To: Multiple recipients of list ORACLE-L 
>Subject: FRAGMENTATION ISSUE? 
>Date: Thu, 07 Feb 2002 13:07:10 -0800 
> 
>Hi 
>one of my tablespace is highly fragmented and that tablespace 
>contains 
>almost all objects and its default tablespace of one of users .I 
>want to 
>correct it thru export/import.Let me correct if any thing missing 
>please. 
>-Full export of current instance. 
>-Drop that tablespace including contents.(Drop only one tablespace) 
>-Create that tablespace 
>-import the data thru fromuser touser option. 
>-compile the object if required 
>Is anything missing in this list.The above are overall steps.Let me 
>know 
>this will remove the fragmentation of that tablespace or not?What is 
>the 
>risk factor to do this? 
>Thx 
>-sEEMA 
> 
> 
> 
>_ 
>Chat with friends online, try MSN Messenger: 
>http://messenger.msn.com 
> 
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com 
>-- 
>Author: Seema Singh 
> INET: [EMAIL PROTECTED] 
> 
>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: [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). 

  _  

Send and receive Hotmail on your mobile device: Click Here
 
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
Shaibal Talukder INET: [EMAIL PROTECTED] 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:
[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: Miller, Jay
  INET: [EMAIL PROTECTED]

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

RE: Fragmentation of data dictionary

[K Gopalakrishnan]

Title: Fragmentation of data dictionary




Helmut,
 
X$ 
tables are NEVER stored in the disk. THey are just memory structures in the SGA 
and the contents are zeroed (are reset) when you shutdown the database. THey 
will not cause data dictionary fragmentation.
 
 
Best Regards,K GopalakrishnanBangalore, 
INDIA

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Daiminger, HelmutSent: 
  Tuesday, February 12, 2002 5:53 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Fragmentation of data 
  dictionary
  Hi! 
  I was wondering whether the Oracle data dictionary gets 
  fragmented and whether the dba needs to do something about it. E.g. if 
  granting tons of rights through grants directly to a user (and later revoke 
  them), does the data dictionary get fragmented (i.e. the x$ tables that hold 
  that information)? Would it be beneficial to reorganise the dd (or at least 
  rebuild the indexes)?
  Same thing with creating tons of temporary tables that are 
  created during a session and get dropped at the end of a session. Does this 
  fragment the dd?
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut 

Re: FRAGMENTATION ISSUE?

[Shaibal Talukder]

Seema,
From your posting, it is not clear to me about the content of the mentioned tablespace. If it contains all of one users schema you can just take export for that particular schema. The step when you are recreating the tablespace, create it with pct increase 0 and do create initial and next extents same size or you can create the tablespace as a locally managed tablespace - this is where your fragmentation is going to be stopped. The rest looks fine to me.
Slightly off topic, it is not good practice to create everything belong to a owner in the same tablespace. For sure tables and indexes should have their seperate tablespaces. 
Shaibal

>From: "Seema Singh" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED] 
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: FRAGMENTATION ISSUE? 
>Date: Thu, 07 Feb 2002 13:07:10 -0800 
> 
>Hi 
>one of my tablespace is highly fragmented and that tablespace 
>contains 
>almost all objects and its default tablespace of one of users .I 
>want to 
>correct it thru export/import.Let me correct if any thing missing 
>please. 
>-Full export of current instance. 
>-Drop that tablespace including contents.(Drop only one tablespace) 
>-Create that tablespace 
>-import the data thru fromuser touser option. 
>-compile the object if required 
>Is anything missing in this list.The above are overall steps.Let me 
>know 
>this will remove the fragmentation of that tablespace or not?What is 
>the 
>risk factor to do this? 
>Thx 
>-sEEMA 
> 
> 
> 
>_ 
>Chat with friends online, try MSN Messenger: 
>http://messenger.msn.com 
> 
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com 
>-- 
>Author: Seema Singh 
> INET: [EMAIL PROTECTED] 
> 
>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: [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). 
Send and receive Hotmail on your mobile device: Click Here
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shaibal Talukder
  INET: [EMAIL PROTECTED]

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

RE: Fragmentation & Locally Managed Tablespaces

[Hillman, Alex]

And this only matters for queries for data dictionary views. I also beleave
that number of extents matter if you have parallel processing. I saw
something about it on Johnathan Lewis web site.

Alex Hillman

-Original Message-
Sent: Thursday, June 28, 2001 8:36 AM
To: Multiple recipients of list ORACLE-L




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"  |
||  |
||   |
||  06/28/01 |
||  10:46 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >---|
  |   |
  |   To: Multiple recipients of list ORACLE-L|
  |   <[EMAIL PROTECTED]>  |
  |   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 <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>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: [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).

_
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: [EMAIL PROTECTED]

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: [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: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
--

Re: Fragmentation & Locally Managed Tablespaces

[Paul Drake]

yep.

what I was wondering was at what point for storage in general, are the
"points of inflection" for # of segments per tablespace, # of extents
per segment - where a performance hit occurs, when the header block
overflows.

I saw that a tablespace had > 34000 extents in it - but then saw that
only 1 segment had > 505 extents - so I let it go.

just didn't want the extra I/O fetch every time.

Paul

Rachel Carmichael wrote:
> 
> Mike,
> 
> Thanks, I didn't really believe Oracle would "take back" the maxextents
> unlimited option, I just thought it was funny that the recommendation was
> now going back to the original extent recommendations :)
> 
> Rachel
> 
> >From: [EMAIL PROTECTED]
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: Re: Fragmentation & Locally Managed Tablespaces
> >Date: Thu, 28 Jun 2001 04:35:35 -0800
> >
> >
> >
> >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
> >
> >
> _
> 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: [EMAIL PROTECTED]
> 
> 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: [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: Paul Drake
  INET: [EMAIL PROTECTED]

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

Re: Fragmentation & Locally Managed Tablespaces

[Rachel Carmichael]

Mike,

Thanks, I didn't really believe Oracle would "take back" the maxextents 
unlimited option, I just thought it was funny that the recommendation was 
now going back to the original extent recommendations :)

Rachel


>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: Fragmentation & Locally Managed Tablespaces
>Date: Thu, 28 Jun 2001 04:35:35 -0800
>
>
>
>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
>
>
_
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: [EMAIL PROTECTED]

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

RE: Fragmentation & Locally Managed Tablespaces

[paquette stephane]

According to the Oracle documentation, it's the OS
block size. 


 --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > 
> Thanks indeed for the Article's Excerpt , Paul 
> 
> Our db_block_size=8K
> 
> 
> > -Original Message-
> > From:   Paul Drake [SMTP:[EMAIL PROTECTED]]
> > Sent:   Thursday, June 28, 2001 1:46 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject:Re: Fragmentation & Locally Managed
> Tablespaces
> > 
> > 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: [EMAIL PROTECTED]
> > 
> > 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: [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: VIVEK_SHARMA
>   INET: [EMAIL PROTECTED]
> 
> 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: [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). 

=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]

___
Do You Yahoo!? -- Pour faire vos courses sur le Net, 
Yahoo! Shopping : http://fr.shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: [EMAIL PROTECTED]

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

Re: Fragmentation & Locally Managed Tablespaces

[MHately]

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"  |
||  |
||   |
||  06/28/01 |
||  10:46 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >---|
  |   |
  |   To: Multiple recipients of list ORACLE-L|
  |   <[EMAIL PROTECTED]>  |
  |   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 <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>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: [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).

_
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: [EMAIL PROTECTED]

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: [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: 
  INET: [EMAIL PROTECTED]

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

Re: Fragmentation & Locally Managed Tablespaces

[Rachel Carmichael]

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



>From: Paul Drake <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>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: [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).

_
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: [EMAIL PROTECTED]

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

RE: Fragmentation & Locally Managed Tablespaces

[VIVEK_SHARMA]

Thanks indeed for the Article's Excerpt , Paul 

Our db_block_size=8K


> -Original Message-
> From: Paul Drake [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, June 28, 2001 1:46 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:      Re: Fragmentation & Locally Managed Tablespaces
> 
> 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: [EMAIL PROTECTED]
> 
> 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: [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: VIVEK_SHARMA
  INET: [EMAIL PROTECTED]

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

Re: Fragmentation & Locally Managed Tablespaces

[Paul Drake]

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

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

Re: Fragmentation & Locally Managed Tablespaces

[paquette stephane]

3000 extents seems a lot to me.

I usually kept the number of extents for a segment
below the number of blocks kept in the extent map, the
extent map depends on the OS block size. For example
if the block size is 8K you should not have more than
504 extents.




 --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > 
> 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 ?
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: VIVEK_SHARMA
>   INET: [EMAIL PROTECTED]
> 
> 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: [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). 

=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]

___
Do You Yahoo!? -- Pour faire vos courses sur le Net, 
Yahoo! Shopping : http://fr.shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: [EMAIL PROTECTED]

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