Re: Maximum size a tablespace can grow to

See....I told you it was off the top of my head.......Change the line with DECODE to something like the following:

SELECT
tablespace_name,SUM(DECODE(SIGN(maxbytes-bytes),-1,0,maxbytes-bytes)) as extend_bytes

Again. Off the top of my head...

HTH,
Brian

"R.B" wrote:
>
> 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"

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

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"