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

Thread: Fregmentation

  1. #1
    Join Date
    Mar 2001
    Posts
    131
    Hi,

    I want to know the fregmented Tablespace, fregmented objects (tables, Indexes......) How do i know this ? and what criteria have to decide so that i can say it is fregmented ?

    Thanks,
    Upeshp

  2. #2
    Join Date
    May 2002
    Posts
    42
    you can use the DBMS_SPACE package.
    you can also query dba_segments and do it the old fashion way. if you have multiple entries in dba_segments for the same object and the blocks used are not sequential then the table is framented.

    use DBMS_SPACE it is easy and gives lots of information. I think you can do a desc dbms_space to see the procedures available.

    Andrew

  3. #3
    Join Date
    May 2002
    Posts
    42
    sorry, that may be dba_extents not dba_segments.

    Andrew

  4. #4
    Join Date
    Mar 2001
    Posts
    131
    I think i did not deliver the question correctly..!!

    I have query written for finding out the fregmented segments.......that is based on the number of extents......Now i want to know which value will justify that it is fregmented.


  5. #5
    Join Date
    Mar 2001
    Posts
    131
    Hi

    I did not get you about the sentence below, can u please explain me ??

    "if you have multiple entries in dba_segments for the same object and the blocks used are not sequential then the table is framented. "

    Thanks

  6. #6
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    There is also freespace fragmentation.
    Check dba_free_space for amount of entries of freespace.

    select tablespace_name, max(bytes/1024/1024), min(bytes/1024/1024), count(*)
    from dba_free_space
    group by tablespace_name;

    check count(*) column, if more than 1000, possible fragmentation.

    If you move to LMT, there is no need to worry about fragmentation.

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  7. #7
    Join Date
    Mar 2001
    Posts
    131
    Thanks Sureshy,

    You mean to say if extents ( count (*) )allocated to tablespace is more than 1000 then i have to consider it as a fregmented ......?

    Please reply.


  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Number of extents has nothing to do with fragmentation. See http://technet.oracle.com/deploy/per...pdf/defrag.pdf for details.
    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