Re: SQL Help

From: D.Y. <dyou98_at_aol.com>
Date: 13 Aug 2002 11:17:37 -0700
Message-ID: <f369a0eb.0208131017.162f6397@posting.google.com>

"Joe Philip" <joe.philip_at_verizon.net> wrote in message news:<dxU59.2984$Kx4.560_at_nwrddc03.gnilink.net>... ...
>
> select a.tablespace_name, sum(a.bytes) "Free in TEST", sum(b.bytes) "used in
> PROD", sum(a.bytes) - sum(b.bytes) "Fit"
> from dba_free_space a, dba_segmets_at_prod b
> where a.tablespace_name=b.tablespace_name
> group by a.tablepsace_name;
>

You are counting segments and free space too many times. Try this.

select a.tablespace_name,a.free_space,b.used_space,a.free_space-b.used_space from (select tablespace_name,sum(bytes) free_space from dba_free_space

      group by tablespace_name) a,
     (select tablespace_name,sum(bytes) used_space from dba_segments_at_prod
      group by tablespace_name) b

where a.tablespace_name=b.tablespace_name;

If you have no free space or segments in any of the tablespaces, even this query won't give you correct results. You'll then have to use outer joins.

> When I run this query, I do not get any errors. Instead the values reported
> for "Free in TEST" is kind of sum of free space from TEST and PROD.
>
>
>
>
>
> "John Findlay" <john_findlay_at_non.agilent.com> wrote in message
> news:3D57EE78.5060300_at_non.agilent.com...
> > Daniel Morgan wrote:
> > > Joe Philip wrote:
> > >
> > >
> > >>I want to find the free space and used space from a database. I wrote
> the
> > >>following sql but does not work:
> > >>
> > >>select sum(a.bytes) free, sum(b.bytes) used
> > >>from dba_free_space a, dba_segments b
> > >>where a. tablespace_name=b.tablespace_name
> > >>group by a.tablespace_name
> > >>/
> > >>
> > >>What am I doing wrong?
> > >
> > >
> > > A number of things. But the most obvious is that you did not post the
> error
> > > message or the version which should be a standard part of every posting.
> > >
> > > In this case the most obvious problem is that you have a GROUP BY clause
> and
> > > nothing being grouped.
> > >
> > > Group by would only be of value if you rewrote your query in this form:
> > >
> > > select A.TABLESPACE_NAME, sum(a.bytes) free, sum(b.bytes) used
> > > from dba_free_space a, dba_segments b
> > > where a. tablespace_name=b.tablespace_name
> > > group by a.tablespace_name
> > > /
> > >
> > > Daniel Morgan
> > >
> >
> > Daniel is quite correct. You may wish to try the following query:
> >
> > /* If running this in a narrow (80 character) terminal emulator */
> > /* you may wish to cut the linesize down to match, in which case the
> > lines will wrap */
> > set pages 30
> > set linesize 200
> > clear columns
> > clear breaks
> > column tbsp format A25 heading 'Tablespace'
> > column filenam format A30 heading 'File'
> > column fsz format 99999.999 heading 'File Size MB'
> > column tott format 99999.999 heading 'Free MB'
> > column numm format 999999 heading 'No. of chunks'
> > column maxx format 99999.999 heading 'Max Chunk MB'
> > column avgg format 99999.999 heading 'Avg Chunk MB'
> > column minn format 99999.999 heading 'Min Chunk MB'
> > break on tbsp skip1
> > select
> > substr(ts1.tablespace_name,1,25) tbsp,
> > df1.file_id, substr(df1.file_name,1,30) filenam,
> > df1.bytes/(1024*1024) fsz,
> > sum(fsp1.bytes)/(1024*1024) tott,
> > count(*) numm,
> > max(fsp1.bytes)/(1024*1024) maxx,
> > avg(fsp1.bytes)/(1024*1024) avgg,
> > min(fsp1.bytes)/(1024*1024) minn
> > from
> > sys.dba_tablespaces ts1,
> > sys.dba_data_files df1,
> > sys.dba_free_space fsp1
> > where
> > df1.tablespace_name = ts1.tablespace_name
> > and fsp1.file_id(+) = df1.file_id
> > group by ts1.tablespace_name, df1.file_id, df1.file_name,
> > df1.bytes/(1024*1024)
> > /
> >
> > The result will tell you not only how much space there is but also
> > the number of chnuks it comes in etc..
> >
> > You'll need to remove the formatting if using it in a tool other than
> > SQL*Plus (e.g Toad). Run it as SYS or another account with relevant
> > privileges.
> >
> > John S. Findlay
> >
Received on Tue Aug 13 2002 - 13:17:37 CDT