/*
 * Title  : sho_db_io.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: This script shows the I/O generated per day.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 80
set pagesize 0
set pause off

set heading off
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

spool &&rpt_dir/sho_db_io.rpt

select 
	'Datafiles: '|| to_char((((a.io)*c.bs)/d.days)/(1024*1024*1024),'999,999.00') || ' GB/Day' || chr(10) ||
	'Tempfiles: '|| to_char((((b.io)*c.bs)/d.days)/(1024*1024*1024),'999,999.00') || ' GB/Day' || chr(10) ||
	'Total    : '|| to_char((((a.io+b.io)*c.bs)/d.days)/(1024*1024*1024),'999,999.00') || ' GB/Day'
from
	(
		select
			sum(phyblkrd+phyblkwrt) io
		from
			v$filestat
	) a,
	(
		select
			sum(phyblkrd+phyblkwrt) io
		from
			v$tempstat
	) b,
	(
		select 
			value bs
		from 
			v$parameter 
		where 
			name='db_block_size'
	) c,
	(
		select 
			sysdate-startup_time days
		from 
			v$instance
	) d;

spool off

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80

set termout on
set serverout off
set feedback on
set echo off
