Re: SQL joins ?

Peter,

No need to do a join, try the following select:

	select substr(a.file_name,1,15) file_name,
	       substr(a.tablespace_name,1,10) table_space_name,
	       sum(a.bytes) total,sum(b.bytes) free
	  from dba_data_files a, dba_free_space b
	 where a.tablespace_name = b.tablespace_name
       group by a.file_name,a.tablespace_name:

Result is:  

FILE_NAME TABLE_SPAC TOTAL FREE --------------- ---------- ---------- ----------

/u02/oradb/PQTS INDEXES     660602880   17784832
/u03/oradb/PQTS INDEXES     660602880   17784832
/u05/oradb/PQTS RBS         104857600   84369408
/u02/oradb/PQTS SYSTEM       73400320   61530112
/u03/oradb/PQTS TEMP       1.6463E+10   71999488
/u04/oradb/PQTS USERS      1468006400  194715648
/u05/oradb/PQTS USERS      1468006400  194715648

Hope this helps,

Vikram Goel

Sr. DBA

Aerotek Consulting Inc.

Ft. Lauderdale, FL

respond to vgoel_at_emi.net

>> Views expressed are my own and not of my clients, past, present or future. <<

In article <4ctju7$tc_at_gate.bton-health.sthames.nhs.uk>, peter_at_gate.bton-health.sthames.nhs.uk (Peter Gross) writes:
>
>I am using the following script that provides with the right output, but
>not in the format I would like :-(
>
>
>select file_id, file_name, tablespace_name, bytes
> , 'Total' status
>from dba_data_files
>union
>select file_id, ' ' file_name
> , tablespace_name, sum(bytes), 'Free' status
>from dba_free_space
>group by file_id, tablespace_name
>order by 3 asc, 1 asc, 5 desc
>/
>This gives the following output:
>
> ID FILE-NAME TABLE-SPACE BYTES STATUS
>--- ---------------------- ----------- ---------- ------
> 1 /usr4/business_1.dbs BUSINESS 52428800 Total
> 1 BUSINESS 39792640 Free
>What I would like is:
>
> ID FILE-NAME TABLE-SPACE TOTAL FREE
>--- ---------------------- ----------- ---------- ----------
> 1 /usr4/business_1.dbs BUSINESS 52428800 39792640
>
>
>
>Peter Gross.
Received on Wed Jan 10 1996 - 00:00:00 CET