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

Thread: Best method???

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Red face

    Hi all,

    Oracle 805/ NT 4.

    Say only 10% of tablespaces have Free space fragmentation. What is the best method to remove fragmentation from these Tablespaces.

    I was told by one of friends that it does not hurt the performance if the segment has too many extents.

    Is it True?

    Thanks in Adv.








    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, it is true.

    But don't spread this info around too much - if this becomes a widely known fact, many DBA's jobs might be endangered. It seems to me (concluding from many simmilar posts in this forum) that the whole work of many DBAs is concentrated around "eliminating tablespace fragmentation" fight. Once their bosses find out that fragmentation isue is not performance isue at all, they might get fired.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    c**L.

    What about the first question???

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The best way to eliminate a tablespace fragmentation in 805?

    - Export all objects from that tablespace.
    - Drop all objects from that tablespace
    - Coalesce the tablespace
    - Reimport those objects into the tablespace

    This is not only "the best method", it is "the only metod available" to eliminate fragmentation in 805.

    However all this is just to eliminate the symptoms of fragmentation, not to eliminate the cause of the fragmentation. If you wan't to eliminate the fragmentation permanently you must use the same size for all your extents in that tablespace. That is INITIAL=NEXT, PCTFREE=0 for any segment created in that tablespace.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks Jurij,

    In our case, database is not designed in-house and supplied with Oracle Financials ERP.

    I checked that maximum segments initial and next extents are not matching with that of their tablespaces.

    In such case, what should I do?

    Should I atleast make initial and next extents same for all segments falling under same tablespace?

    Pl. advise.

    Thanks.



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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