no. of extent in tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: no. of extent in tablespace

  1. #1
    Hi,

    I need some help regarding the no. of extents in tablespace that has been allocated. In my dba_tablespace, few tablespaces was set to maxextent of 1024. But when I query dba_extents or dba_segments for no. of extents group by tablespace I got a result of bigger than 1024.

    Am I missing something here? Anybody could explain to me, please? What I want to do is to get total no. of extent for all tablespaces available in my database.

    regards,
    feroz

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> select tablespace_name, count(*)
    from dba_extents
    group by tablespace_name;

    TABLESPACE_NAME COUNT(*)
    ------------------------------ ----------
    INDX 207
    SYSTEM 754
    TEMP 94
    TOOLS 232
    USERS 192

    ...
    About 1024 extents :
    Don't worry about that, this is "INTERNAL" extents for keep db objects,
    U have to worry about "EXTERNAL" extents in AUTOEXTENT clause
    for commad alter tablespace add datafile ...,
    but u can just set this parameter to UNLIMITED.
    ----------------------------------------------------------------------------
    NOTE:
    parameter maxextent --> is parameter of DATAFILE (not tablespase),
    and doesn't matter where u set this parameter:
    -- in create tablespace command or
    -- in alter tablespace add datafile.

    [Edited by Shestakov on 07-09-2002 at 11:07 AM]

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Probably, you are mistaken with DBA_SEGMENTS column MAX_EXTENTS which represents the 'Maximum # of extents of segment '
    Reddy,Sam

  4. #4
    Hi,

    I used the same query to get the no. of extents for each tablespace. What I'm confuse here is the maxextent set to 1024 in dba_tablespaces but the query result is more than 1024.

    Anybody could explain why it goes more than 1024?

    regards,
    feroz

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    See more detail infomation about tablespaces, extents ...

    http://download-east.oracle.com/otnd...4space.htm#924
    http://download-east.oracle.com/otnd...block.htm#2528

    This is answer for ur question.

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try this query and see the results match your max_extents defined in tablespace storage clause or not.

    select tablespace_name, max_extents
    from dba_tablespaces
    group by tablespace_name ,max_extents
    Reddy,Sam

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I think you are confused with the same/similar column name of different views. The definition of column of same/similar name is different from one view to another view.

    Check out Oracle8i Reference for descriptions of the DBA views.
    Reddy,Sam

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    I think u see 1024 as maxextents in dba_tablespaces:

    in dba_tablespaces this parameter describe DEFAULT VALUE of STORAGE clause
    and !!! this parameter act FOR EACH DB OBJECT SEPARETLY (not for whole tablespace)
    when u create new db object (table, index ...), that has extents,
    and u don't describe value of this parameter (in STORAGE clause) espesialy for
    new object in create statment.

    Then if u want to check # of extents for each object in tablespases u can use:
    SQL> select tablespace_name, owner, segment_name, segment_type, count(*)
    from dba_extents
    group by tablespace_name, owner, segment_name, segment_type
    having count(*) > 50;
    TABLESPACE_NAME OWNER SEGMENT_NAME SEGMENT_TYPE COUNT(*)
    ------------------ ---------------------- ------------------------------------------------- ----------- ----------
    SYSTEM SYS SOURCE$ TABLE 64
    TEMP SYS 4.2 TEMPORARY 94

  9. #9
    Hi,

    Thank you for pointing me to correct way.


    regards,
    feroz

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