3) # "INSTANCE INFORMATION"

3) # "INSTANCE INFORMATION"

sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
Prompt # INSTANCE INFORMATION                                       #
prompt ##############################################################
col name for a60
col host_name for a30
col SERVER for a40
col "INSTANCE NAME" for a15
col version for a12
col "STARTUP TIME" for a20
col status for a15
select inst_id AS "INSTANCE ID"
   , a.instance_name AS "INSTANCE NAME"
   , a.host_name AS "SERVER"
   , a.version
   , a.status
   , a.instance_role as INST_ROLE
   , b.DATABASE_ROLE as DB_ROLE
   , a.active_state
   , to_char(a.startup_time,'dd/mm/yyyy hh24:mi') as "STARTUP TIME"
   , case when a.startup_time < sysdate then 'Status OK' when a.startup_time < sysdate - 7 then 'DB Restarted' else 'Verify Restarted DB' end as "Status DB"
from gv\$instance a, v\$database b
order by 1;
prompt
prompt ##############################################################
Prompt # PGA                                                        #
prompt ##############################################################
show parameter pga;
prompt
prompt ##############################################################
Prompt # SGA                                                        #
prompt ##############################################################
show parameter sga;
prompt
prompt ##############################################################
PROMPT # SHARED POOL                                                #
prompt ##############################################################
select name, to_char(bytes/1024, '999G999D99') as SIZE_KB, to_char(bytes/1024/1024, '999G999D999') as SIZE_MB, to_char(bytes/1024/1024/1024, '999D999') as SIZE_GB, RESIZEABLE from v\$sgainfo order by name;
prompt
prompt ##############################################################
Prompt # SUM SGA                                                    #
prompt ##############################################################
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 500
SET FEEDBACK OFF
select round(tot.bytes/1024/1024 ,2) total_mb
  , round(used.bytes/1024/1024 ,2) used_mb
  , round(free.bytes/1024/1024 ,2) free_mb
  , round(tot.bytes/1024/1024/1024 ,2) total_GB
  , round(used.bytes/1024/1024/1024 ,2) used_GB
  , round(free.bytes/1024/1024/1024 ,2) free_GB
  , round(tot.bytes/1024/1024/1024/1024 ,2) total_TB
  , round(used.bytes/1024/1024/1024/1024 ,2) used_TB
  , round(free.bytes/1024/1024/1024/1024 ,2) free_TB
from (select sum(bytes) bytes from v\$sgastat where name != 'free memory') used
   , (select sum(bytes) bytes from v\$sgastat where name = 'free memory') free
   , (select sum(bytes) bytes from v\$sgastat) tot;
Prompt
quit;
EOF