How do I join these 2 sql scripts

[Tison]

I have the following 2 scripts that show tablespace usage and record counts. I want to merge them into one ;
=======================================
CLEAR
SET HEAD ON
SET VERIFY OFF
SET PAGES 200

PROMPT
ACCEPT tablespace_name PROMPT 'Enter Value Tablespace (Return For all) : '

col tspace form a20 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"

break on report
compute sum of tot_ts_size on report
compute sum of free_ts_size on report

select df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
and df.tablespace_name like UPPER('%&tablespace_name%')
group by df.tablespace_name, df.bytes
order by df.tablespace_name
/
quit
========================================
select tablespace_name,sum(num_rows) from dba_tables
group by tablespace_name
========================================

 

[appi]

Hi,

Try to include in script .... But I guess the formats for the percentages are not quiet6ly correct.


CLEAR
SET HEAD ON
SET VERIFY OFF
SET PAGES 200

PROMPT
ACCEPT tablespace_name PROMPT 'Enter Value Tablespace (Return For all) : '

col tspace form a20 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"
col nrows form 999,999,999 Heading "Num_Rows"

break on report
compute sum of tot_ts_size on report
compute sum of free_ts_size on report

select df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1,
sum(dt.num_rows) nrows
from dba_free_space fs, dba_tables dt, (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
and df.tablespace_name like UPPER('%&tablespace_name%')
group by df.tablespace_name, df.bytes
order by df.tablespace_name
/
quit
HTH

Uwe

 

[sonksen]

Hello Tison

Try this:

clear
set head on
set verify off
set pages 200
set lines 200

prompt
accept tablespace_name prompt 'Enter Value Tablespace (Return For all) : '

col tspace form a20 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"
col row_count form 999,999,999,990 Heading "Record Count"

break on report
compute sum of tot_ts_size on report
compute sum of free_ts_size on report
compute sum of row_count on report

select df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1,
dt.row_count
from dba_free_space fs,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) df,
(select tablespace_name, sum(num_rows) row_count
from dba_tables
group by tablespace_name) dt
where fs.tablespace_name = df.tablespace_name
and fs.tablespace_name = dt.tablespace_name
and df.tablespace_name like UPPER('%&tablespace_name%')
group by df.tablespace_name, df.bytes, dt.row_count
order by df.tablespace_name;

Regards,
Ronald