SQL joins ?

From: Raimonds Buls <Raimonds.Buls_at_f5.n5100.z2.fidonet.org>
Date: 1996/01/10
Message-ID: <3cb_9601101752_at_mailer.castle.riga.lv>#1/1

Hello Peter!

Tuesday January 09 1996, Peter Gross writes to All:

 PG> I am *very* rusty with my SQL, can someone help me with the following
 PG> problem displaying _both_ the free space and the total space available
 PG> by o/s file and tablespace.

 PG> What I would like is:

 PG>  ID FILE-NAME              TABLE-SPACE      TOTAL       FREE
 PG> -+- -+--+--+--+--+--+--+-- -+--+--+--- -+--+--+-- -+--+--+--
 PG>   1 /usr4/business_1.dbs   BUSINESS      52428800   39792640

Try thus :

/*--------------------------------------------------------------------*/

column file_id format 99 heading 'ID'
column Total format 999999999999 heading 'Total bytes' column Free format 999999999999 heading 'Free bytes' column Pct format 990.9999 heading 'Pct free' column tablespace_name format a25 heading 'TABLE-SPACE' column file_name format a30 heading 'FILE-NAME'

/* For Tablespace */

SELECT a.tablespace_name Tablespace_name ,

       SUM(a.bytes) Total ,
       SUM(f.bytes) Free ,
       ( SUM(f.bytes) / SUM(a.bytes) ) * 100 Pct
FROM dba_data_files a ,
     dba_free_space f

WHERE a.tablespace_name = f.tablespace_name GROUP BY a.tablespace_name
ORDER BY 1 ;
/* For Datafile - Tablespace */

SELECT a.file_id ,
       a.file_name file_name  ,
       a.tablespace_name  Tablespace_name  ,
       a.bytes Total ,
       f.bytes Free ,
       ( f.bytes / a.bytes ) * 100 Pct
FROM dba_data_files a ,
     dba_free_space f

WHERE a.file_id = f.file_id
ORDER BY 3 asc , 2 asc ;

/*--------------------------------------------------------------*/

Raimonds Buls
  ( FidoNet 2:5100/5 ) Received on Wed Jan 10 1996 - 00:00:00 CET