Tablespace max size query

Hi ,

I need to get the maximum size for an table size ,
I used the below command to create the tablespace
Create tablespace  sam datafile '+Vol01' size 20G autoextend on next 1G maxsize 31G;.
Alter  tablespace  sam add datafile '+Vol01' size 20G autoextend on next 1G maxsize 31G;.

We have RAC environment , two instances of the database running.

I need the below clarification in regard to tablespace

1. what is the difference between Tablespace and datafiles ?
2. Once the initial size is full , to which data file does oracle autoextend to 1GB
3. How to get the Total  size , USED size and Free Size ofthe tablespace and datafiles .
           I tried the below query , not sure about the output

Select bytes , MAxBytes from DBA_DATA_FILES . Is it correct.

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:
1. A tablespace is a logical collection of one of more datafiles.

2. It autoextends by 1G increments up to the maxsize of 31Gig.

3. Used can be a couple of things:  Allocated space that doesn't have data in it yet or actual space used by data.  Which one are you after?

While we wait for the answer, take a look at the following views:  dba_free_space view and dba_extents.
sam_2012Author Commented:
For Question 1: How to determine the tablespace size and datafile size is it same?

I want to know both for question 3.
slightwv (䄆 Netminder) Commented:
>>I want to know both for question 3.

There are many queries out there on the Web that report what you are after.  Actual used space is pretty difficult to generate and personally, I've never had the need to get those numbers.

Allocated space can be found from the dba_extents view.  Again, there are many queries people have already written and posted.

>>For Question 1: How to determine the tablespace size and datafile size is it same?

Tablespaces don't have a size per say.  Datafile size can be found from dba_data_files.

Tablespaces have segments assigned to them.  The segments use space.  You can get the individual space a segment uses from the dba_segments view.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam_2012Author Commented:
Ian bit confused with segments and tablespace releation. In general , Tablespace is a logical collection of tablespace , how segemnts are related to tablespace?
slightwv (䄆 Netminder) Commented:
I strongly encourage you to spend some time in the Concepts Guide.  It does a really good job at explaining Oracle's architecture.

In a nutshell:
A tablespace is made up of one or more datafiles.
A tablespace contains one or more segments.
A segment is made up of one or more extents.
An extent is one or more data blocks.

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
sam_2012Author Commented:
Can you share the query , that you used to get the tablespace Total Size , Total Spaced used , total space available.
slightwv (䄆 Netminder) Commented:
I don't have these in my sql folders.  Whenever I do need them, I Google for them.  There are MANY of them already written and posted out there.
sam_2012Author Commented:
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.