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

Thread: Highly fragmented tables

  1. #1
    Join Date
    Jul 2001
    Posts
    108
    Hello,

    I looking for the script which will show the highly fragmented tables in the database.

    Thanks,
    Seenu

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Thumbs up


    analyze the schema and then:
    select * from dba_tables where chain_cnt > 0;

    - Magnus

  3. #3
    Join Date
    Jul 2001
    Posts
    108
    Hello Magnus ,

    The script which you have provided will give us the table information which has chained rows, but there are some tables which has fragmentation may not have chained rows.

    Thanks,
    Seenu

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy


    you mean migrated rows?

    define FRAGMENTATION and I'll be more specific for you.

    - Magnus

  5. #5
    Join Date
    Feb 2001
    Posts
    129
    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)

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    number of extents does NOT affect fragmentation, define what do you mean by fragmentation?

  7. #7
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking


    thanks for the backup Pando!
    ;-)


  8. #8
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    I though fragmentation was the number of extents?
    That's why it has it's own view (dba_extents).

    I guess it could be exanded to segments, since a segment could be made up of multiple extents, but I don't know if I want to be that generous.

    select segment_name,count(*) from dba_extents
    where segment_type='TABLE'
    group by segment_name;
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    fragmentation is based on those free extents not contiguos and one here one there you see them everywhere but just not contiguos! (and even after coalesce there are loads of them)

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