Re: Maximum size a tablespace can grow to

Brian,

Thanks for the pointer, I can use your code to find the free space for each datafile but it is not letting me use the GROUP BY function on DECODE(SIGN(maxbytes-bytes) it returns:

ORA-00979: not a GROUP BY expression

do you know of any way around this ?

Thanks

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F12DAB2.C40736F1_at_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 Tue Jul 15 2003 - 05:32:36 CDT