Current extent number and max tablespace extent number

Posted on 2004-11-16
Last Modified: 2008-03-17

I am checking on the current extents number in a tablespace comparing with the max extents allowed for a tablespace. I find out the former is greater than the latter. Aren't we not allowed to surpass the max extents of a tablespace. Could anyone tell me what is going on? Here is the script I used:

select a.tablespace_name, a.total_extents, b.max_extents from (select tablespace_name, count(*) total_extents
from dba_extents group by tablespace_name) a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name
Question by:ebi168
    LVL 76

    Accepted Solution

    The flaw is the join to dba_extents.  

    You are almost correct in the assumption:  No single object can have more than max_extents for a tablespace.  

    in other words:  max_extents at the tablespace level is for each individual object in it, not for all objects total.

    Author Comment

    Then what about max_extent in dba_segments, must it be at most the max_extent of the tablespace? I found that some of the values are greater than tablespace max_extent.  Does it mean you can specify it, but you could never reach any point greater than the tablespace max_extent? Thanks
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)

    The max extents for the tablespace is the default value for any object in the tablespace that did not explicitly specify it.  The rule for storage management goes as follows:

    Object level
    Tablespace level
    System tablespace

    If you don't specify max_extents at object creation time, it will default to the values of the tablespace.  If the tablespace does not have default values, it will use max_extents from the system tablespace.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to recover a database from a user managed backup

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now