Printable View
I read this on another site. Its a sql command to find out if the DB is fragmented.
select * from dba_segments where extents > 10;
After I run this almost all of my tables are above 10, which the author of this article says "In general, if a table or index has more than 10 extents then rebuild it to fit into one extent". This is a pretty new DB and hasn't been used that much. Is this good advice?
Thanks Paul
Don't trust everything you read out there.
Here in my shop we have several tables holding in excess of one billion rows apiece, how could them fit in a single extent? BS!
Everything depends on volume.
For your five more critical tables please post...
1- Quantity of rows
2- Average row lenght
3- Initial/Next extent size
I disagree, it is a sql query that will tell you what has more than 10 extents. A table or index having more than 10 extents is not a sign of fragmentation. If you are using locally managed tablespaces than you have have tables with extents in the thousands and not see an issue.
If you want to avoid fragmentation use locally managed tablespaces with either automatic storage of reasonably sized uniform extents.
Thanks for the replies.. We are using a RAC setup with ASM.. Does ASM account for the fragmentation?
What does that have to do with fragmentation?Quote:
Originally Posted by PAVB
have you seen what OP thinks fragmentation is? :rolleyes: Just trying to use the same language of the user does, otherwise no conversation is possible.Quote:
Originally Posted by ixion
No, it doesn't.Quote:
Originally Posted by pam61
There used to be a concern with dictionary managed tablespaces over segments with extremely ghigh numbers of extents, and in fact the number of extents used be be severely limityed in early versions of Oracle.
With locally managed tablespaces there should be a very much lower level of concern, certainly not in the range the author suggests.
This seems to be the definitive word on the subject of fragmentation, http://technet.oracle.com/deploy/ava...pdf/defrag.pdf