I want to know the fregmented Tablespace, fregmented objects (tables, Indexes......) How do i know this ? and what criteria have to decide so that i can say it is fregmented ?
you can use the DBMS_SPACE package.
you can also query dba_segments and do it the old fashion way. if you have multiple entries in dba_segments for the same object and the blocks used are not sequential then the table is framented.
use DBMS_SPACE it is easy and gives lots of information. I think you can do a desc dbms_space to see the procedures available.
sorry, that may be dba_extents not dba_segments.
I think i did not deliver the question correctly..!!
I have query written for finding out the fregmented segments.......that is based on the number of extents......Now i want to know which value will justify that it is fregmented.
I did not get you about the sentence below, can u please explain me ??
"if you have multiple entries in dba_segments for the same object and the blocks used are not sequential then the table is framented. "
There is also freespace fragmentation.
Check dba_free_space for amount of entries of freespace.
select tablespace_name, max(bytes/1024/1024), min(bytes/1024/1024), count(*)
group by tablespace_name;
check count(*) column, if more than 1000, possible fragmentation.
If you move to LMT, there is no need to worry about fragmentation.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
You mean to say if extents ( count (*) )allocated to tablespace is more than 1000 then i have to consider it as a fregmented ......?
Number of extents has nothing to do with fragmentation. See
http://technet.oracle.com/deploy/per...pdf/defrag.pdf for details.
Click Here to Expand Forum to Full Width