Re: SQL joins ?

In article <1996Jan10.125606.27184_at_cnet.fr>, lhabitan_at_belfort.cnet.fr (Jean-Marc LHABITANT) writes:
|> In article <4ctju7$tc_at_gate.bton-health.sthames.nhs.uk>, peter_at_gate.bton-health.sthames.nhs.uk (Peter Gross) writes:
|> |> 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.
|> |>
|> |> --
|> |> The ideas, suggestions, jokes, cautions, complaints, comments, scripts, insults,
|> |> critiscms, observations, diagrams, sarcastic remarks, stories, programs, and
|> |> thoughts shown in the text above are mine and mine alone, and are NOT those of
|> |> my employer or anyone else for that matter (unless I say otherwise ;-)
|>
|>
|> I don't know if this is the best way, but it works ! So you can try it.
|>
|> create table free_temp as select file_id, sum(bytes) "FREE"
|> from dba_free_space
|> group by file_id;
|>
|> column file_id format 99 heading 'ID';
|> column file_name format a40 heading 'FILE-NAME';
|> column tablespace_name format a11 heading 'TABLE-SPACE';
|>
|> select d.file_id, d.file_name, d.tablespace_name, d.bytes "Total", f.FREE "Free"
|> from dba_data_files d, free_temp f
|> where d.file_id = f.file_id;
|>
|> drop table free_temp;
|>
|>
|> Good luck !
|>
|> Jean-Marc

I think I have found the best way !!!

Here is the new script !

column file_id format 99 heading 'ID';
column file_name format a40 heading 'FILE-NAME'; column tablespace_name format a11 heading 'TABLE-SPACE';

select d.file_id, d.file_name, d.tablespace_name, d.bytes "Total", sum(f.bytes) "Free" from dba_data_files d, dba_free_space f
where d.file_id = f.file_id
group by d.file_id, d.file_name, d.tablespace_name, d.bytes;

Ok ? I hope it will help you.

bye, Jean-Marc Received on Fri Jan 12 1996 - 00:00:00 CET