Tablespace Fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tablespace Fragmentation

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    What is actually Tablespace Fragmentation and how does one prevent tablespace fragmentation?

    Sabitabrata

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    One acromyn needed here. LMT. Local Managed Tablespaces with Uniform extents. Kiss all your fragmentation worries goodbye. Alternatively, create different sized Data-Dictionary managed tablespace with same INITIAL and NEXT extent sizes and police them.

    Opps, posted with only half the question answered. Basically fragmentation occurs when objects are created, dropped, rebuilt in the same tablespace in which the objects have different extents sizes. Ie.

    Table EMP (Initial 2M Next 2 M)
    Table DEPT (Initial 1M Next 1M)

    Tablespace occupied

    EMP 2M, DEPT 1M, EMP 2MB.

    If you drop the DEPT table, there with be a gap in the tablespace of 1M (Between the 2 EMP extents). This space will only be reused by a new segment which is <= 1M. And if none are created, you'll basically have 1M wsted. Hence fragmentation. This incident is more evident with indexes in which are rebuilt. Simple because when and index is rebuilt, the space it occupied prior to the rebuild is freed. Therefore creating freespace gaps in the tablespace.

    The idea is to create tablespace in which every object has the same initial/next extents, so the space can be re-use by other segment extents.

    Cheers

    [Edited by grjohnson on 02-01-2002 at 12:33 AM]
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Aug 2001
    Posts
    64
    Good question! I was wondering about this myself. But what
    dictionary table do I query to find out if the tablespace is
    fragmented? Thanks.

  4. #4
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    DBA_FREE_SPACE_COALESCED.
    Check for PERCENT_EXTENTS_COALESCED column's data. If for any tablespace this is not 100, then that tablespace needs coalescing

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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