Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: How can I find the next extent size for a table in 10g?

    I have recently upgraded from 9i to 10g and I have a query to tell me the next extent size on all my tables so I can check to see if there is enough free space, if not then I can add another file to the database.

    select distinct
    segment_name,
    segment_type "TYPE",
    tablespace_name "TABLESPACE",
    bytes,
    extents,
    next_extent,
    max_extents
    from user_segments
    where segment_type in ('TABLE')

    in 10g it seems that the next extent size is automatically decided by oracle. Where can I find that figure?

    The tablespace has extent_managment LOCAL and segment_space_management AUTO

    For example I have a table that has
    initial extent : 57344
    extents : 503
    bytes : 21474836480

    So all the extents are not 57344, how can I find what the next extent size will be?

    I currently have 80 databases with massively different useages so I monitor space daily with the query to see which databases are running low on space.

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    initial = next for LMT
    there is no next since they all will be the same extent size
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    thats what I expected, but my table is 21474836480 bytes with an initial extent of 57344 and 503 extents, so that doesn't work out right
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    What exactly are you trying to accomplish??
    Are you attempting to check the math that oracle uses?
    What is the point of this exercise?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    if you are checking Oracle's math then please account for BLOCK SIZE.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by The_Duck
    What exactly are you trying to accomplish??
    Are you attempting to check the math that oracle uses?
    What is the point of this exercise?
    I have 80 databases accross a dozen servers that vary in size from 3 gig to 70 gig. In 9i I had a script that ran through and checked all the databases to make sure there was enough room for two more of the largest next extents in the tablespace, if not it emailed me and I added another file to the tablespace.

    As there is no Next extent size in user_segments in 10g I need to work out when I need to add another datafile to a database

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Duck, if you have auto segment management the LMT doesnt have unform extent sizes, of the top of my head I believe it uses 3 different extent sizes, so as your table grows the extent size goes up thus handling both small and large tables efficiently.

    Anyway regarding detecting when your tablespace is nearly full, most DBAs dont highlight where the next extent cant be allocated but rather have some arbitary limit like identify tablespaces which are 90% full. The reason for this is if you have lots of different tables in a tablespace even if you have enough space for the next extent you might not have enough space if two tables both decide to extend at the same time.

    Here is the sql I use which detects when the used amount is greater than 80% of the maximum autoextended size of the tablespace. The problem with all these scripts and one unfortunately Enterprise Manager (ignore the max autoextend size) still cant do is to combine say the query below with the amount of free space on the disk.

    Code:
    select      "Tablespace", trunc(sum("Used MB")), sum(filesize), sum(maxsize)
    from
    (
    SELECT      d.file_id, t.tablespace_name  "Tablespace",  t.status "Status", 
    			filesize, maxsize,      
    			filesize - free "Used MB",  free "Free MB" ,   
    			t.initial_extent "Initial Extent",     t.next_extent "Next Extent",    t.min_extents "Min Extents",   t.max_extents "Max Extents",   
    			t.pct_increase "Pct Increase"   , SUBSTR(d.file_name,1,80) "Datafile name"  
    FROM        (SELECT tablespace_name, file_id, ROUND(SUM(bytes)/1024/1024,2) free from DBA_FREE_SPACE group by tablespace_name, file_id) f, 
    			(SELECT tablespace_name, file_id, file_name, ROUND((bytes/1024/1024)) filesize, ROUND((maxbytes/1024/1024)) maxsize from DBA_DATA_FILES ) d , 
    			DBA_TABLESPACES t  
    WHERE      	t.tablespace_name = d.tablespace_name    
    AND 		f.tablespace_name(+) = d.tablespace_name    
    AND 		f.file_id(+) = d.file_id
    ) group by "Tablespace"
    having trunc(sum("Used MB"))*1.2 > sum(maxsize)
    Alan

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by AlanP
    Duck, if you have auto segment management the LMT doesnt have unform extent sizes, of the top of my head I believe it uses 3 different extent sizes, so as your table grows the extent size goes up thus handling both small and large tables efficiently.
    Alan
    I can't disagree with you and can only say that I have not seen many implementations which use that specific functionality. I plead lack of experience/knowledge!

    Here is a little ditty which checks what the original poster wanted to check:
    PHP Code:
    set heading on
    set feedback on
    set timing off
    set verify off
    set 
    echo off
    set trimspool on
    set lines 200
    set pagesize 500
    set tab off

    col owner           heading 
    'Owner'           format a10 
    col segment_name    heading 
    'Segment Name'    format a30
    col tablespace_name heading 
    'Tablespace Name' format a20
    col next_extent     heading 
    'Next Extent'     format 9,999,999,999
    col max_extents     heading 
    'Max|Ext'         format a7   justify right 
    col ext_left        heading 
    'Ext|Left'        format 999 
    col extents         heading 
    'Ext|Used'        format 9999  

    Prompt 
    &&2 Segments that can not extend &&1 times given the free space:

    select
      segment_name

      
    tablespace_name
      case 
    when next_extent is null then
        
    (select max(e.bytes)
           
    from dba_extents e
           where e
    .segment_name segment_name)
         else 
    next_extent end next_extent,
      
    extentsext_left,
      case 
    when max_extents 2147483645 
      then 
    'Unlmtd' 
      
    else to_char(max_extentsend max_extents
    from 
    (select
            s
    .segment_name
            
    s.tablespace_name,
            
    s.next_extent
            
    s.extents
            
    s.max_extents,
            
    sum(trunc(fs.bytes/s.next_extent)) ext_left,
            (
    select count(*)
               
    from   sys.dba_free_space fs2
               where  s
    .tablespace_name fs2.tablespace_name
               
    and    s.next_extent <= fs2.bytestotal
             from
               sys
    .dba_segments ssys.dba_free_space fs
             where
               s
    .tablespace_name fs.tablespace_name
               
    and s.segment_type in 
                 
    ('TABLE','TABLE PARTITION','INDEX','INDEX PARTITION')
               and 
    s.tablespace_name not in ('SYSTEM',  'TOOLS')
             
    group by
               s
    .segment_names.partition_names.tablespace_name,
               
    s.next_extents.extentss.max_extents)
    where &&ext_left and &&total
    order by 2 desc

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    HI Duck

    Actually I wasnt strictly correct. If you have extent management set to autoallocate then it uses variable extent sizes. If its set to uniform then it uses the extent size you define on the tablespace.

    Alan

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Yes, as I understand it, variable extent sizes varies depending on how historically your table has grow. Oracle anticipate based on this to make the decision to grow larger or smaller.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •