REM DISCLAIMER OF WARRANTIES.
REM THE SCRIPT IS PROVIDED TO CUSTOMER ON AN “AS-IS” BASIS.  FLASHGRID AND 
REM ITS SUPPLIERS DISCLAIM ALL EXPRESS, IMPLIED OR STATUTORY WARRANTIES 
REM RELATING TO THE SCRIPT, INCLUDING BUT NOT LIMITED TO, MERCHANTABILITY, 
REM FITNESS FOR A PARTICULAR PURPOSE, TITLE, AND NON-INFRINGEMENT. 
REM FLASHGRID DOES NOT WARRANT THAT USE OF THE SCRIPT WILL BE UNINTERRUPTED OR 
REM ERROR-FREE, THAT DEFECTS WILL BE CORRECTED, OR THAT THE SCRIPT IS FREE 
REM OF HARMFUL COMPONENTS. FLASHGRID IS NOT REQUIRED TO PROVIDE ANY MAINTENANCE 
REM OR SUPPORT SERVICES WITH RESPECT TO THE SCRIPT.

set lin 32767 num 20 numf 999999999990

alter session set nls_numeric_characters='.,';

col sga_target for a20
col pga_aggregate_target like sga_target

set markup html on

spo output.html

select i.inst_id,
       i.instance_name,
       i.host_name,
       i.version,
       (select name from gv$database where inst_id = i.inst_id) db_name,
       (select db_unique_name from gv$database where inst_id = i.inst_id) db_unique_name,
       (select round(value/1024/1024) from gv$parameter p where p.inst_id = i.inst_id and p.name='pga_aggregate_target') pga_aggregate_target_mb,
       (select round(current_size/1024/1024) from gv$memory_dynamic_components where inst_id = i.inst_id and component = 'PGA Target') pga_target_current_mb,
       (select round(value/1024/1024) from gv$parameter p where p.inst_id = i.inst_id and p.name='sga_target') sga_target_mb,
       (select round(current_size/1024/1024) from gv$memory_dynamic_components where inst_id = i.inst_id and component = 'SGA Target') sga_target_current_mb,
       (select round(value/1024/1024) from gv$parameter p where p.inst_id = i.inst_id and p.name='sga_max_size') sga_max_size_mb,
       (select round(value/1024/1024) from gv$parameter p where p.inst_id = i.inst_id and p.name='memory_target') memory_target_mb,
       (select round(value/1024/1024) from gv$parameter p where p.inst_id = i.inst_id and p.name='memory_max_target') memory_max_target_mb,
       (select max(s."AVERAGE")
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read IO Requests Per Sec') max_avg_read_iops,
       (select max(s.maxval)
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read IO Requests Per Sec') max_max_read_iops,
       (select max(s."AVERAGE")
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Total IO Requests Per Sec') max_avg_total_read_iops,
       (select max(s.maxval)
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Total IO Requests Per Sec') max_max_total_read_iops,
       (select max(s."AVERAGE")/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Bytes Per Sec') max_avg_read_mbps,
       (select max(s.maxval)/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Bytes Per Sec') max_max_read_mbps,
       (select max(s."AVERAGE")/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Total Bytes Per Sec') max_avg_total_read_mbps,
       (select max(s.maxval)/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Read Total Bytes Per Sec') max_max_total_read_mbps,
       (select max(s."AVERAGE")
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write IO Requests Per Sec') max_avg_write_iops,
       (select max(s.maxval)
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write IO Requests Per Sec') max_max_write_iops,
       (select max(s."AVERAGE")
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Total IO Requests Per Sec') max_avg_total_write_iops,
       (select max(s.maxval)
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Total IO Requests Per Sec') max_max_total_write_iops,
       (select max(s."AVERAGE")/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Bytes Per Sec') max_avg_write_mbps,
       (select max(s.maxval)/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Bytes Per Sec') max_max_write_mbps,
       (select max(s."AVERAGE")/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Total Bytes Per Sec') max_avg_total_write_mbps,
       (select max(s.maxval)/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Physical Write Total Bytes Per Sec') max_max_total_write_mbps,
       (select max(s."AVERAGE")/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Redo Generated Per Sec') max_avg_redo_mbps,
       (select max(s.maxval)/1024/1024
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'Redo Generated Per Sec') max_max_redo_mbps,
       (select max(s."AVERAGE")/100
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'CPU Usage Per Sec') max_avg_db_cpu_per_sec,
       (select max(s.maxval)/100
          from gv$database d,
               dba_hist_sysmetric_summary s
         where d.inst_id = i.inst_id
           and s.dbid = d.dbid
           and s.instance_number = d.inst_id
           and s.metric_name = 'CPU Usage Per Sec') max_max_db_cpu_per_sec,
       (select value
          from gv$osstat
         where inst_id = i.inst_id
           and stat_name = 'NUM_CPU_SOCKETS') cpu_sockets,
       (select value
          from gv$osstat
         where inst_id = i.inst_id
           and stat_name = 'NUM_CPU_CORES') cpu_cores,
       (select value
          from gv$osstat
         where inst_id = i.inst_id
           and stat_name = 'NUM_CPUS') cpu_threads,
       (select value/1024/1024
          from gv$osstat
         where inst_id = i.inst_id
           and stat_name = 'PHYSICAL_MEMORY_BYTES') host_ram_mb
  from gv$instance i
 order by inst_id;

spo off

set markup html off
exit