Hello all ,
today i was defragmenting my fragmented tables .....
i defragmented my objects by
1) taking a full export of the user
2) dropping the user (cause a lot of the objects in this user were fragmented)
3) recreating the user (this user had a locally managed tablesapce as its default tablespace)
4) importing the users' object back.
now i ran my query (to check for fragmentation) again , but saw that the object showed the same fragmentation intensity.
now this was a peculiar site , as i had done this before , and got the desired defragmentation.
So now what i did was ,
1) i droped the user again.
2) i droped the locally managed tablespace
3) i recreated the user with a dictionary managed tablespace as its default tablespace.
4) i imported the users objects
this time i ran the query and found that the objects were defragmented (and i was happy again) :)
So here i concluded that when objects are stored in a locally manged tablespace , they cannot be defragmented , but when they are stored in a dictonary managed tablespace , they can be defragmented.
PLEASE EXPLAIN THE ABOVE PHENOMENON , IF POSSIBLE
First of all there is no possibility in LMT for fragmentation cuz, always your inital and next extents are uniform no matter its auto allocation or manual allocation and PCTINCREASE zero
Something missing here, as you are saying the objects in LMT are fragmented.
Fragmentation is based on freespace not on the number of extents, here you are clearly talking about your number of extents is high which in any case is NOT the fragmentation as we understand here
Did you define uniform extent in the locally managed tablespace?
[Edited by tamilselvan on 07-13-2001 at 02:12 PM]
Defrag in LMT
I think there is no need to bother either of fragmentaion ( as there is none) or of large number of extents in LMT. The way space management is done in LMT with bitmap of allocated blocks, it does not matter.
As per some documentation, even if there are thounds of extents in LMT, it does not degrade performance.