set linesize 2500
set pagesize 1000

column created                format a20;
column date_of_run                format a20;
column open_time                  format a20;
column block_size_bytes           format a20;
column host_name           format a10;
column archiver           format a10;
column timed_statistics           format a20;

select d.name SID, i.host_name , i.version, i.status,
       i.archiver, d.log_mode, p.value timed_statistics 
  from v$database d, v$parameter p,
       v$thread t, v$instance i
 where p.name = 'timed_statistics';

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
       to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
       to_char(created, 'DD-MON-YYYY HH24:MI:SS') CREATED,
       value BLOCK_SIZE_BYTES
  from v$database d, v$parameter p,
       v$thread t, v$instance i
 where p.name = 'db_block_size';

column event format a50;
column waits format 999,999,999;
column time  format 999,999,999;

select event, waits, time     
  from (select e.event       event
             , e.total_waits waits
             , e.time_waited time
             , e.total_timeouts total_timeouts
         from  v$system_event e
         where e.event not in
               ( 'Null event',
		'PX Idle Wait',
		'SQL*Net message from client',
		'SQL*Net message to client',
		'SQL*Net more data from client',
		'client message',
		'dispatcher timer',
		'lock manager wait for remote message',
		'parallel query dequeue',
		'pipe get',
		'pmon timer',
		'rdbms ipc message',
		'smon timer',
		'virtual circuit status',
		'wakeup time manager')
         order by time desc, waits desc
     )
where rownum <= 10
/

column TOTALS                     format a44               
column file_system_name           format a44               
column tablespace_name            format a44               
column file_name                  format a54               
column MBytes                     format 9,999,999         
column phyblkrd                   format 9,999,999         
column phyblkwrt                  format 9,999,999         
column read_time_per_block_msec   format 9,999,999         
column write_time_per_block_msec  format 9,999,999         
column avg_access_time_tot        format 9,999,999         
column avg_access_time            format 9,999,999         
column avg_access_speed           format 9,999,999         
column avg_estimated_srcer_speed  format 9,999.999         
column avg_estimated_rlser_speed  format 9,999.999         
column tps                        format 9,999.999         
column spt                        format 9,999.999         

select substr(name,1,instr(name,'/',-1))||substr(name,1,instr(name,'\',-1)) file_system_name,
       sum(bytes)/(1024*1024) MBytes,
       sum(phyblkrd)/1000 phyblkrd,
       sum(phyblkwrt)/1000 phyblkwrt,
       avg((readtim*10)/(phyblkrd+1)) read_time_per_block_msec,
       avg((writetim*10)/(phyblkwrt+1)) write_time_per_block_msec,
       (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd)/1000 + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt)/1000) /
           (sum(phyblkrd)/1000 + sum(phyblkwrt)/1000 + 1)
            avg_access_time,
       ((sum(phyblkrd) + sum(phyblkwrt)) /
           (avg((readtim*10)/(phyblkrd+1))*sum(phyblkrd) + avg((writetim*10)/(phyblkwrt+1))*sum(phyblkwrt) + 1)) * 1000
            avg_access_speed
  from v$datafile a,
       v$filestat b
 where a.file# = b.file#
 group by substr(name,1,instr(name,'/',-1)), substr(name,1,instr(name,'\',-1))
 order by 7 desc, 6 desc
/ 
  
column name     format a30        heading 'SGA regions';
column value    format 999,999,999,990 heading 'Size in Bytes';

break on report;
compute sum of value on report;
ttitle lef 'SGA Memory Summary ' 

select name
     , value
  from v$sga
 order by name;
clear break compute;