Fregmentation

[Upeshp]

Hi,

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 ?

Thanks,
Upeshp

[alenz]

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.

Andrew

[alenz]

sorry, that may be dba_extents not dba_segments.

Andrew

[Upeshp]

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.

[Upeshp]

Hi

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

Thanks

[Sureshy]

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(*)
from dba_free_space
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.

[Upeshp]

Thanks Sureshy,

You mean to say if extents ( count (*) )allocated to tablespace is more than 1000 then i have to consider it as a fregmented ......?

Please reply.

[marist89]

Number of extents has nothing to do with fragmentation. See http://technet.oracle.com/deploy/per...pdf/defrag.pdf for details.