How to determine MAXSIZE of existing tablespace

Question

I need to determine the MAXSIZE that was set for a tablespace when it was created (Oracle 10g)

I'm sure I'm missing something obvious, but the information isn't immediately apparent in the information in DBA_TABLESPACES.

Answer 1

In 11g this query would give you the answer, but I notice you're on 10g and alas the useful column is missing.

select tablespace_name, max_size 
from dba_tablespaces
/

In 10g you will have to

select tablespace_name
       , initial_extent + (next_extent * (max_extents-1)) as calc_max_size
from dba_tablespaces 
/

Remember that this is the default maximum size. In practice you will be limited by the size of the datafiles assigned to the tablespace, which might be much less than this theoretical maximum.

edit

@Paul 's comment is pertinent. I suppose the correct answer would be to say that the maximum size of a tablespace is a meaningless, indeed almost fictional, concept. The size of a tablespace is actually determined by its datafiles, and its potential maximum maximum size is determined by the maximum number of datafiles which can be assigned. The SQL Reference has this to say on the topic:

• A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
• A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million ([2 to the power of 22]) blocks.

So perhaps this is a more useful query ...

select tablespace_name
       , count(*) as no_of_data_files
       , sum(maxblocks) as max_size
from dba_data_files
group by tablespace_name
/

...with the caveat that it only applies to the currently assigned datafiles.

edit 2

MAXSIZE applies to the datafile not the tablespace. That is why the MAXSIZE keyword is discussed in the documentation for the filespec clause rather than under CREATE TABLESPACE.

Answer 2

It all depends on whether the data file is auto extensible or not.

So you get the the right information from DBA_DATA_FILES:

• If AUTOEXTENSIBLE is set to YES then you need the total sum of MAXBYTES.

• If AUTOEXTENSIBLE is set to NO then you need the total sum of BYTES.

MAX_SIZE in DBA_TABLESPACES has nothing to do with the maximum size of the tablespace itself. According to Oracle documenation it is the

"Default maximum size of segments"

So the right query is:

select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from   DBA_DATA_FILES
group  by TABLESPACE_NAME;

This has been tested on 11g but it should also work on 10g. It gives you the maximum size of each tablespace in bytes.

The same thing goes for TEMP tablespaces:

select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from   DBA_TEMP_FILES
group  by TABLESPACE_NAME;