teachmeoracle.com

[Mahesh]

Hi,

Can anyone tell me how to check whether fragmentation has occured or not and how to resolve it..

[arshad_onlyne]

this is area where iam weak ,told by microsoft

so any ideas guys

[arshad_onlyne]

fragmentatin is automatically achieved by SMON background process if u r using dictionary managed tablespace or else u can manually coalesce free space using

alter table table_name coalesce;

the table created must be belonging to dictionary managed tablepace and also this dictionary tablespaec must not have PCTINCREASE=o or else coalescing is not possible bcoz INITIAL=MINEXTENTS=MAXEXTENTS

[SubbaRao]

What is fragmentation:
If any table is getting inserted with new records,
we dont find any fragmentation. Fragmentation comes with updates and deletes in a table. When we delete, the space which freed up is not
immediately re-used. This leads to holes in table,results in
table fragmentation. same case to updation also.

When you have locally managed tablespaces with uniform extents or automatic segmentspace management, there is no
question of fragmentation.

You will get fragmentation when you are using disctonary managed tablespaces.


How to resolve table fragmentation:

(a) Export the table, drop the table and import again

or

(b) altere table move tablespace < New tablespace Name>

There are so many other things that you need to understand

Check the following asktom link

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1295801859138


Hope it helps,

Good Luck,

Regards,
Subbu