Re: SQL Help

From: John Findlay <john_findlay_at_non.agilent.com>
Date: Mon, 12 Aug 2002 18:20:56 +0100
Message-ID: <3D57EE78.5060300@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 Mon Aug 12 2002 - 12:20:56 CDT