Defragmentation - Locally managed tablespace !
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Defragmentation - Locally managed tablespace !

  1. #1
    Join Date
    Dec 1999
    Location
    Manchester, UK
    Posts
    12

    Smile

    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
    Amit Sanghvi
    Oracle DBA
    Lex/Mastek

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Dude

    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Did you define uniform extent in the locally managed tablespace?


    [Edited by tamilselvan on 07-13-2001 at 02:12 PM]

  5. #5
    Join Date
    Feb 2001
    Posts
    75

    Defrag in LMT

    Hi,

    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.

    Kailash Pareek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width