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

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.

  • 4
  • 4
1 Solution
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.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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.
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:
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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