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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.