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

Thread: Largest free extent

  1. #1
    Join Date
    Jul 2002
    Posts
    132
    Hi,

    I have a tablespace having 5 datafiles each 2Gig. What wud be the largest free extent size? How do I know the largest free extent size?

    Sonia

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Sonia

    TTITLE OFF
    CLEAR COLUMN

    COLUMN tablespace_name FORMAT a16 HEADING "Tablespace"
    COLUMN total_size FORMAT 99,999,999,999 HEADING "Total Size, K"
    COLUMN total_free FORMAT 99,999,999,999 HEADING "Total Free, K"
    COLUMN pct_free FORMAT 999 HEADING "% Free"
    COLUMN largest_free_extent FORMAT 999,999,999 HEADING "Largest Free Ext, K"
    COLUMN chunks FORMAT 999,999 HEADING "Chunks"
    COLUMN largest_next_extent FORMAT 999,999,999 HEADING "Largest Next Ext, K"

    REM

    SELECT S."tsname" AS "tablespace_name",
    Sum(S."tsize")/1024 AS "total_size",
    Sum(S."tfree")/1024 AS "total_free",
    Sum(S."largest_free")/1024 AS "largest_free_extent",
    Sum(S."chunks") AS "chunks",
    Sum(S."tfree")*100/sum(S."tsize") AS "pct_free",
    Sum(S."largest_next")/1024 AS "largest_next_extent"
    FROM
    (SELECT A.tablespace_name AS "tsname",
    0 AS "tsize",
    Sum(A.bytes) AS "tfree",
    Max(A.bytes) AS "largest_free",
    Count(*) AS "chunks",
    0 AS "largest_next"
    FROM dba_free_space A
    WHERE (A.tablespace_name Not Like 'TEMP%'
    AND
    A.tablespace_name Not Like 'RBS%')
    GROUP BY A.tablespace_name
    UNION
    SELECT tablespace_name,
    Sum(bytes),
    0,
    0,
    0,
    0
    FROM dba_data_files
    WHERE (tablespace_name Not Like 'TEMP%'
    AND
    tablespace_name Not Like 'RBS%')
    GROUP BY tablespace_name
    UNION
    SELECT tablespace_name,
    0,
    0,
    0,
    0,
    Max(next_extent)
    FROM dba_segments
    WHERE segment_type Not In ( 'TEMPORARY', 'ROLLBACK')
    GROUP BY tablespace_name) S
    GROUP BY S."tsname"
    HAVING ( Sum(S."largest_free") < Sum(S."largest_next") )
    OR ( Sum(S."tfree")*100/sum(S."tsize") < 3 )
    OR ( (Sum(S."largest_free")/Decode(Sum(S."largest_next"), 0, 0.0001, Sum(S."largest_next"))) < 2 )
    /

    You can run this script.

    regards
    Hrishy

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Sonia

    You could use this script too

    select TABLESPACE_NAME,
    sum(BYTES) Total_free_space,
    max(BYTES) largest_free_extent
    from dba_free_space
    group by TABLESPACE_NAME

    regards
    Hrishy

  4. #4
    Join Date
    Jul 2002
    Posts
    132
    Thanx Hrishy!

    I dont get the list of all tablespaces using ur first script.
    I was using the second script. This is what I found. When I had 5 datafiles of 2Gig each, I found the largest extent as 2G only and not 10G. Again when I had a tablespace of 1G having 2 datafiles of 730m and 270m, I got the largest extent as 730m. Is it right that the largest free extent is the size of the largest free datafile if the extents are coalesces?

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    By Soniaarora
    I dont get the list of all tablespaces using ur first script.
    I was using the second script. This is what I found. When I had 5 datafiles of 2Gig each, I found the largest extent as 2G only and not 10G. Again when I had a tablespace of 1G having 2 datafiles of 730m and 270m, I got the largest extent as 730m. Is it right that the largest free extent is the size of the largest free datafile if the extents are coalesces?
    That is true. If you have no objects in the tablespace and the extents are coalesced then the largest free extent will be the size of the largest datafile in that tablesapce.

    An extent can not spread over mulitple datafile.

    HTH
    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