Re: Help with SQL Puzzle..?

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 4 Feb 1995 20:05:31 GMT
Message-ID: <3h0mmb$6rt_at_dcsun4.us.oracle.com>

qmick_at_pipeline.com (Michael Joseph) writes:

>I am trying to write a one line piece of SQL to return the following data
>
>TABLESPACE FREE SPACE USED SPACE
>
>SYSTEM 10000000 2000000
>UTILS 5000000 3000000
>
>etc.
>
>I have tried this with:
>
>SELECT F.TABLESPACE_NAME, SUM(F.BYTES), SUM(U.BYTES)
>FROM SYS.DBA_FREESPACE F, SYS.DBA_SEGMENTS U
>WHERE F.TABLESPACE_NAME=U.TABLESPACE_NAME
>GROUP BY F.TABLESPACE_NAME
>
>But I get incorrect results coming back.....
>
>How do I make this work...?
>
>MJ
>
>

I know the following query looks ugly but it works great. The bottom of this note shows what the output would look like:

set	wrap off
set	pagesize 66
column dummy noprint
column  pct_used format 999.9       heading "%|Used" 
column  name    format a16      heading "Tablespace Name" 

column bytes format 9,999,999,999,999 heading "Bytes" column used format 99,999,999,999 heading "Used" column free format 999,999,999,999 heading "Free" break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

select a.tablespace_name                                              name,
   b.tablespace_name                                              dummy,
   sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )      bytes,
   sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
   sum(a.bytes)/count( distinct b.file_id )			  used,
   sum(a.bytes)/count( distinct b.file_id )                       free,
   100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))
	   - 
	   (sum(a.bytes)/count( distinct b.file_id ) ))
	   /
	   (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used

from sys.dba_free_space a, sys.dba_data_files b where a.tablespace_name = b.tablespace_name group by a.tablespace_name, b.tablespace_name /

SQL> _at_free

                                                                          %
Tablespace Name               Bytes            Used             Free   Used
---------------- ------------------ --------------- ---------------- ------
CAREDB_INDEX             20,971,520       9,504,768       11,466,752   45.3
CAREDB_USERS             20,971,520       9,601,024       11,370,496   45.8
COBE_TB                   3,145,728         206,848        2,938,880    6.6
XXX                      10,485,760          63,488       10,422,272     .6
IDX                     176,990,208      10,641,408      166,348,800    6.0
SYSTEM                   44,040,192      36,864,000        7,176,192   83.7
TEMP                    176,990,208      20,965,376      156,024,832   11.8
USR                     176,990,208      72,224,768      104,765,440   40.8
                 ------------------ --------------- ----------------
sum                     630,585,344     160,071,680      470,513,664

8 rows selected.

SQL> quit Received on Sat Feb 04 1995 - 21:05:31 CET