set pages 60
set lin 160
set feed off
set termout off
col DATABASE_NAME hea 'Database|Name' for a10 word_wrapped
col TABLESPACE_NAME hea 'Tablespace Name' for a18 word_wrapped
col FILE_NAME hea 'File Name' for a50 word_wrapped
col phyrds hea 'Physical|Reads' for 999,999,999
col phywrts hea 'Physical|Writes' for 999,999,999
col totals hea 'Total I/O' for 9,999,999,999
col db_info new_v db_info
select 'for '||name||' on '||to_char(sysdate, 'YYYY-MM-DD') db_info from v$database;
tti ce '********** Disk I/O Report **********' s 1 -
ce '********** 'db_info' **********' s 2
set termout on
select name database_name, tablespace_name,
decode(count(file_name), 1, min(file_name), '2+ Files') file_name, sum(phyrds),
sum(phyrds) phyrds, sum(phywrts) phywrts, sum(phyrds+phywrts) totals
from ( select b.name, a.tablespace_name, a.file_name, c.phyrds, c.phywrts, a.con_id
from cdb_data_files a
left join v$containers b on a.con_id=b.con_id
left join v$filestat c on a.file_id=c.file#
union
select e.name, d.tablespace_name, d.file_name, f.phyrds, f.phywrts, e.con_id
from cdb_temp_files d
left join v$containers e on d.con_id=e.con_id
left join v$tempstat f on d.file_id=f.file#
)
group by name, tablespace_name, con_id
order by con_id, totals desc;
exit