Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 744
  • Last Modified:

Current extent number and max tablespace extent number


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
  • 2
2 Solutions
slightwv (䄆 Netminder) Commented:
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.
ebi168Author Commented:
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
slightwv (䄆 Netminder) Commented:

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.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now