Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Paris, France
    Posts
    9

    Question Unanswered: number of extents in a table

    Please help me,

    I want to know the request that permits to know the number of extents in a table or per table.

    Moreover, I'm also looking for a request (or a PL/SQL block) that helps to know the |ratio of use of a rollback segment for one request.

    Thanks to all

    bye

  2. #2
    Join Date
    Feb 2002
    Location
    British Columbia
    Posts
    13
    There probably are several ways but this is quick and easy:

    SELECT COUNT(extent_id) "EXTENTS" FROM DBA_EXTENTS
    WHERE OWNER = 'schema_owner'
    AND SEGMENT_NAME = 'table_name';

  3. #3
    Join Date
    Jan 2002
    Location
    west haven
    Posts
    33
    Extent Reporting Script (ORAMAG)

    This tip about comes from Adrian Shepherd, Database Administrator in Farnborough, Hants.

    It is important for a DBA to pro-actively manage object space requirements to avoid getting the "next extent isn't available" error. Some tools only report on the availability of the next extent; in a fast growing database, this is not enough. This script will open the reporting window by taking into account the NEXT and NEXT + 1 extent.

    ************** START OF CODE
    set serverout on
    set feed off
    DECLARE

    M_OWNER dba_segments.owner%type;
    M_SEGMENT_NAME dba_segments.segment_name%type;
    M_TYPE dba_segments.segment_name%type;
    M_TSNAME dba_segments.segment_name%type;
    M_BYTES dba_segments.bytes%type;
    M_NEXT dba_segments.next_extent%type;
    M_PCTINC dba_segments.pct_increase%type;
    NEXT_NEEDED1 number;
    NEXT_NEEDED1_MB number;
    NEXT_NEEDED2 number;
    NEXT_NEEDED2_MB number;
    LARGEST_HOLE number;
    LARGEST_HOLE_MB number;
    ROW_COUNT number := 0;


    CURSOR get_segment_name is
    select rpad(owner,12,' '),
    rpad(segment_name,20,' '),
    rpad(segment_type,13,' '),
    tablespace_name,
    bytes,next_extent, pct_increase
    from dba_segments
    order by tablespace_name;

    BEGIN



    dbms_output.enable(1000000);
    dbms_output.put_line
    ('List of objects that will fail to allocate next/next+1 extent.');
    dbms_output.put_line
    ('Obj Type Owner Name NEXT MB 2nd NEXT Free');
    dbms_output.put_line(lpad('_',67,'_'));

    open get_segment_name;
    LOOP
    fetch get_segment_name into
    m_owner,m_segment_name,m_type,m_tsname,m_bytes,m_n ext,m_pctinc;

    select max(bytes) into largest_hole from dba_free_space
    where tablespace_name=M_TSNAME;

    if m_pctinc = 0 then m_pctinc := 100;
    end if;

    exit when get_segment_name%NOTFOUND;

    next_needed1 := m_next;
    next_needed2 := m_next + m_next*(m_pctinc/100);
    next_needed1_MB := round(next_needed1/(1024*1024));
    next_needed2_MB := round(next_needed2/(1024*1024));
    largest_hole_MB := round(largest_hole/(1024*1024));

    select max(bytes) into largest_hole from user_free_space
    where tablespace_name=M_TSNAME;

    if (
    next_needed1 > largest_hole
    OR
    next_needed2 > largest_hole
    )
    then
    row_count := row_count +1 ;
    dbms_output.put_line
    (M_TYPE||M_OWNER||M_SEGMENT_NAME||
    rpad(next_needed1_MB,9,' ')||
    rpad(next_needed2_MB,9,' ')||
    rpad(largest_hole_MB,9,' '));
    end if;

    END LOOP;
    close get_segment_name;
    END;
    /
    set feed on;


    ***************** USAGE
    Tested on 7.1, 8.0 and 8.1
    SQL> alter table sizex storage (next 1M);

    Table altered.

    SQL> @cant_grow
    List of objects that will fail to allocate next/next+1 extent.
    Obj Type Owner Name NEXT MB 2nd NEXT Free
    __________________________________________________ _________________
    SQL>
    SQL>
    SQL>
    SQL> alter table sizex storage (next 100M);

    Table altered.

    SQL> @cant_grow
    List of objects that will fail to allocate next/next+1 extent.
    Obj Type Owner Name NEXT MB 2nd NEXT Free
    __________________________________________________ _________________
    TABLE OEM2 SIZEX 100 200 180
    SQL> alter table sizex storage (next 181M);

    Table altered.

    SQL> @cant_grow
    List of objects that will fail to allocate next/next+1 extent.
    Obj Type Owner Name NEXT MB 2nd NEXT Free
    __________________________________________________ _________________
    TABLE OEM2 SIZEX 181 362 180
    SQL> alter table sizex storage (next 1M);

    Table altered.

    SQL> @cant_grow
    List of objects that will fail to allocate next/next+1 extent.
    Obj Type Owner Name NEXT MB 2nd NEXT Free

Posting Permissions

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