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

Thread: Fragment

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    Is this script the best one to find fragmentation in segments:


    SELECT segment_name table_name , COUNT(*) extents
    FROM dba_segments
    WHERE owner NOT IN ('SYS', 'SYSTEM')
    GROUP BY segment_name
    HAVING COUNT(*) = (SELECT MAX( COUNT(*) )
    FROM dba_segments
    GROUP BY segment_name)
    /
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well number of extents is not really fragmentation

    plus that script is wrong, you dont need to count to get number of extents, there is a column called extents in dba_segmentS!

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The script above won't return any useful data. Check ths scripts available in the script section of this website.
    http://www.dbasupport.com/oracle/scr...Fragmentation/

    Sanjay

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Actually Farrokhp has taken one of the script that's given in the URL given by
    Sanjay_G. The No. of extents allocated for a table helps a little to find out
    the fragmentation in a segment. As far as fragmentation in a segment is concerned,
    it happens due to large no. of delete/update statements for the rows existing in a segment.
    The no. of extents for a segment helps in the following way:

    Assume that initially the segment consisted around 10,000 rows which was stored in
    say 10 extents. Then due to heavy deletion and updation activity the no. of records
    had come down to 100 rows. In this case, the 100 rows can be available in any of
    the 10 extents. It need not be contigous. For finding out whether this had happened,
    you can use the ROWID psuedocolumn and get the information from DBA_EXTENTS.

    But this is automatically de-fragmented when inserts happen for the next time. If you find that
    a segment is heavily fragmented then you can export and import the table's records.
    This is known as "Re-Organization" of tables.

    Vijay.

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I am not sure whether this script is from dbasupport.com but it is wrong. Note that he is selecting from dba_segments (not dba_extents) and grouping by segment_name.. If he wants to know number of segments one could simply select "extents" from dba_segments..
    Sanjay

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