Re: Maximum size a tablespace can grow to

Thanks Brian - the final queries I ran were

SQL> SELECT
tablespace_name,sum(DECODE(SIGN(maxbytes-bytes),-1,0,maxbytes-bytes)) as extend_bytes

           FROM dba_data_files GROUP BY tablespace_name;

and

SQL> SELECT a.tablespace_name,a.extend_bytes+b.free_space AS

            total_possible_space
            FROM   (SELECT

tablespace_name,sum(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;

Which gave different totals - I think the second one is adding the free space in the tablespace to the total free space after the datafiles have fully extended. So the first SQL statement is the one to use - Do you agree ?

Thanks

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F1400B3.1BCFBBEC_at_remove_spam.peasland.com...
> 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"
Received on Tue Jul 15 2003 - 10:39:01 CDT