--
--      tsinfo.sql
--      tablespace & datafiles information
--

set pagesize 66
set echo off
set verify off
set feedback off

ttitle 'Free Space by Tablespace'

col col1        format a20  heading "Tablespace"
col col2        format 999,999,999,999        heading "Bytes(KB)"
col col3        format 999,999,999,999        heading "Used(KB)"
col col4        format 9999,999,999        heading "Free(KB)"
col col5        format 999.9    heading "Free(%)"

--spool tsinfo.lst

select x.a col1, sum(x.b) col2, sum(x.c) col3, sum(x.b)-sum(x.c) col4,
      (sum(x.b)-sum(x.c))/sum(x.b)*100 col5
      from (
                    select b.tablespace_name a,sum(bytes)/1024 b,0 c
                    from dba_data_files b
                    group by b.tablespace_name
                    union
                    select d.tablespace_name,0,sum(bytes)/1024
                    from dba_segments d
      group by d.tablespace_name ) x
      group by x.a
      order by x.a
/

prompt
prompt

ttitle 'Data Files'

column file_name format a32;
column tablespace_name  format a15 heading 'Tablespace';
column bytes    format 9,999,999 heading 'Bytes(KB)';
column status    format a10;
break on tablespace_name skip

select tablespace_name, file_name,bytes/1024 bytes, status
from dba_data_files
/

prompt

ttitle 'Disk I/O per Disk Files'
col name        heading 'Name'                  format a30
col phyrds      heading 'Physical Reads'        format 999,999,990
col phywrts    heading 'Physical Writes'      format 999,999,990
col sum(phyrds+phywrts) heading 'SUM(Physical I/O)'    format 999,999,990

select  name, phyrds, phywrts, sum(phyrds+phywrts)
from    v$datafile df, v$filestat fs
where  df.file# = fs.file#
group by name,phyrds,phywrts
order by 4,3,2,1
/
prompt

-- spool off
set feedback on
set verify on