RE: Free space for all tablespaces

From: Jack Silvey <JSilvey_at_XOL.com>
Date: Tue, 11 Jul 2000 12:12:10 -0500
Message-Id: <10555.111690@fatcity.com>

Exactly.

It is because you are getting multiple records per tablespace for those tablespaces with multiple datafiles.

If a tablespace has >1 datafiles, you will have >1 records, which will cause you to have >1 records per tablespace from dba_free_space.

-----Original Message-----
From: Sah Kohsuwan [mailto:skohsuwan_at_comforce.com] Sent: Tuesday, July 11, 2000 12:41 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Free space for all tablespaces

Tom,
Try this one...
select a.tablespace_name tablespace, round(sum(a.bytes)/1024/1024,2) Used, round(nvl(b.free_space,0),2) Free
  from dba_data_files a,

      (select tablespace_name, sum(bytes)/1024/1024 free_space
         from dba_free_space
         group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+) group by a.tablespace_name, b.free_space order by 1;

HTH,
- Sah Kohsuwan

> -----Original Message-----
> From: blair_at_pjm.com [SMTP:blair_at_pjm.com]
> Sent: Tuesday, July 11, 2000 12:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Free space for all tablespaces
>
> Thanks for the reply but... I have tried this. For some reason that I
> don't
> understand it seems to fail when the tablespace has more than 1 datafile.
> It
> does not generate an error - it just gives the wrong answer - really
> weird.
>
> thanks anyway,
>
> ..tom
>
>
>
>
>
>
> > -----Original Message-----
> > From: Suhen Pather [SMTP:pathers5_at_telkom.co.za]
> > Sent: Tuesday, July 11, 2000 10:58 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Free space for all tablespaces
> >
> > You can try this it works
> >
> > select a.tablespace_name, sum(a.bytes/1024/1024) "Allocated" ,
> > sum(b.bytes/1024/1024) "Free Space" from sys.dba_data_files a,
> > sys.dba_free_space b
> > where a.tablespace_name = b.tablespace_name
> > group by a.tablespace_name
> > /
> >
> > Regards
> > $uhen
> > Oracle DBA
> > Telkom SA
> >
> >
> > >>> blair_at_pjm.com 07/11/00 03:56PM >>>
> > I just want a SQL query to give me the freespace in all tablespaces.
> This
> > doesn't work:
> >
> > select a.tablespace_name, sum(a.bytes) TOTAL_SPACE, sum(b.bytes)a
> FREE_SPACE
> > from sys.dba_data_files a, sys.dba_free_space b
> > where a.tablespace_name = b.tablespace_name
> > group by 1;
> >
> > Does anyone have a query that does work??
> >
> > thanks,
> >
> > ..tom
> >
> >
> >
> > --
> > Author:
> > INET: blair_at_pjm.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > --
> > Author: Suhen Pather
> > INET: pathers5_at_telkom.co.za
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Author:
> INET: blair_at_pjm.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Author: Sah Kohsuwan
  INET: skohsuwan_at_comforce.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Tue Jul 11 2000 - 12:12:10 CDT