Re: Maximum size a tablespace can grow to

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Mon, 14 Jul 2003 16:30:42 GMT
Message-ID: <3F12DAB2.C40736F1@remove_spam.peasland.com>

You'll have to query DBA_DATA_FILES to find out how much space the datafiles can extend to.

SELECT tablespace_name,DECODE(SIGN(maxbytes-bytes),-1,0,maxbytes-bytes) as extend_bytes
FROM dba_data_files
GROUP BY tablespace_name;

Couple that with the amount of free space, and the query you've already indicated:

SELECT a.tablespace_name,a.extend_bytes+b.free_space AS total_possible_space
FROM (SELECT
tablespace_name,DECODE(SIGN(maxbytes-bytes),-1,0,maxbytes-bytes) as extend_bytes

        FROM dba_data_files
        GROUP BY tablespace_name) a,
       (SELECT tablespace_name,SUM(bytes) as free_space
        FROM dba_free_space
        GROUP BY tablespace_name) b

WHERE a.tablespace_name=b.tablespace_name;

That's just off the top of my head, so you might have to tweak it a little....

HTH,
Brian

"R.B" wrote:
>
> I am trying to find how much space a tablespace can grow to which has
> autoextend on and whos datafiles have a maxsize specified. That is the
> current size of the tablespace minus the maximum potential size of the
> tablespace. I am running the sql below:
>
> SQL> SELECT tablespace_name, trunc ( sum ( bytes ) / (1024*1024) ) as
> free_m FROM dba_free_space GROUP BY tablespace_name;
>
> which I believe is just showing me the free space left in the tablespace
> before the datafiles and tablespace autoextend.
>
> Can anyone tell me how to calculate the "free space" which a require
>
> Thanks,
>
> Richard

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"

Received on Mon Jul 14 2003 - 11:30:42 CDT