Re: SQL joins ?

peter_at_gate.bton-health.sthames.nhs.uk (Peter Gross) wrote:

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

>I am using the following script that provides with the right output, but
>not in the format I would like :-(
 

>#-------------------
 

>column status format a6 heading ''
>column file_id format 99 heading 'ID'
>column bytes format 999999999 heading 'BYTES'
>column tablespace_name format a11 heading 'TABLE-SPACE'
>column file_name format a22 heading 'FILE-NAME'
>
>
>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

>Any ideas on how I should go about this folks ?
 

>Thanks in advance,
>Peter Gross.

Try joining to the view DBA_DATA_FILES using TABLESPACE_NAME as the join column!

Wayne Balmer wainwrights.com wbalmer_at_op.net Received on Mon Jan 15 1996 - 00:00:00 CET