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.

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

Solution 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;

Comments

[Alexander Aleksandrovič Klimov]

Doesn't seem to work for me. First thing was next_extent was null, so just did initial_extent * max_extents, but max_extents seems to be MAXINT (2147483645) always (and the tablespaces were not created with MAXSIZE unlimited)

[Alexander Aleksandrovič Klimov]

It's not an attribute of dba_data_files in Oracle 10g, as far as I can tell (there is a maxbytes), and won't it be the max size of the data file, not the tables space?

[APC]

@Paul - see my revised response. Fundamentally Robert is right: tablsepace size is a product of datafile size.

[Alexander Aleksandrovič Klimov]

I don't want the current space allocated to it. I need the size that was specified with MAXSIZE when it was created. I don't see this as meaningless - presumably Oracle will refuse to extend a tablespace beyond MAXSIZE.

[APC]

@Paul - Oracle will refuse to extend a tablespace beyond the bounds of its currently assigned datafiles (because autoextend is off, or the file system is full, or you've hit the MAXSIZE specified for the datafile).

[Rob van Laarhoven]

Sorry I was not clear. The attribute is called maxbytes. When you look at the ER diagram of oracle : a tablespace consists of one or more datafiles, a datafile has an attribute maxbytes. So the maxsize of a tablepace is the sum of maxbytes of the datafiles in that tablepace.

[Rob van Laarhoven]

Can a tablespace with two datafiles with maxsize=unlimited contain more data than tablespace with one datafile with maxsize=unlimited. ;-)

[Alexander Aleksandrovič Klimov]

thanks - I'd missed that MAXSIZE was a data file thing not a tablespace thing