set lines 120
set pages 999
clear col
set termout off
set trimout on
set trimspool on

col "Setting" format 999,999,999,999
col "MBytes" format 999,999
col inst_id format 999 head "Instance #"
spool parameters.out

break on inst_id skip 2

select inst_id, 'Shared Pool Size'||':  '||decode(value,null,-1,value) "Setting"
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='shared_pool_size'
union
select inst_id, 'Shared Pool Reserved Area'||':  '||decode(value,null,-1,value) "Setting"
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='shared_pool_reserved_size'
union
select inst_id, 'Log Buffer'||':  '||decode(value,null,-1,value) "Setting"
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='log_buffer'
union
select inst_id, 'Streams Pool Size'||':  '||decode(value,null,-1,value) "Setting"
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='streams_pool_size'
union
select inst_id, 'Buffer Cache'||':  '||decode(value,null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_cache_size'
union
select inst_id, 'Recycle Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_recycle_cache_size'
union
select inst_id, 'Keep Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_keep_cache_size'
union
select inst_id, '2K Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_2k_cache_size'
union
select inst_id, '4K Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_4k_cache_size'
union
select inst_id, '8K Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_8k_cache_size'
union
select inst_id, '16K Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_16k_cache_size'
union
select inst_id, '32K Cache'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='db_32k_cache_size'
union
select inst_id, 'Large Pool Size'||':  '||decode(value,null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='large_pool_size'
union
select inst_id, 'Java Pool Size'||':  '||decode(value,null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='java_pool_size'
union
select inst_id, 'SGA Max'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='sga_max_size'
union
select inst_id, 'SGA Target'||':  '|| decode(value, null,-1,value) "Setting" 
   ,(value/1024/1024) "MBytes"
from gv$parameter where name='sga_target'
order by 1, 2
/

col Setting format 999,999,99

select inst_id, 'Session Cached Cursors'||':  '|| decode(value, null,-1,value) "Setting" 
from gv$parameter where name='session_cached_cursors'
union
select inst_id, 'Open Cursors'||':  '||decode(value,null,-1,value) "Setting" 
from gv$parameter where name='open_cursors'
union
select inst_id, 'Processes'||':  '||decode(value,null,-1,value) "Setting" 
from gv$parameter where name='processes'
union
select inst_id, 'Sessions'||':  '||decode(value,null,-1,value) "Setting" 
from gv$parameter where name='sessions'
union
select inst_id, 'DB Files'||':  '||decode(value,null,-1,value) "Setting" 
from gv$parameter where name='db_files'
union
select inst_id, 'Shared Server (MTS)'||':  '||decode(value,null,-1,value) "Setting" 
from gv$parameter where name='shared_server'
order by 1, 2
/


col Setting format a30

select inst_id, 'Cursor Sharing'||':  '|| value "Setting" 
from gv$parameter where name='cursor_sharing'
union
select inst_id, 'Query Rewrite'||':  '||value "Setting" 
from gv$parameter where name='query_rewrite_enabled'
union
select inst_id, 'Statistics Level'||':  '||value "Setting" 
from gv$parameter where name='statistics_level'
union
select inst_id, 'Cache Advice'||':  '||value "Setting" 
from gv$parameter where name='db_cache_advice'
union
select inst_id, 'Use Large Pages'||':  '||value "Setting" 
from gv$parameter where name='use_large_pages'
union
select inst_id, 'Compatible'||':  '||value "Setting" 
from gv$parameter where name='compatible'
order by 1, 2
/

col resource_name format a25 head "Resource"
col current_utilization format 999,999,999,999 head "Current"
col max_utilization format 999,999,999,999 head "HWM"
col intl format a15 head "Setting"

select inst_id, resource_name, current_utilization, max_utilization, initial_allocation intl
from gv$resource_limit
where resource_name in ('processes', 'sessions','enqueue_locks','enqueue_resources',
   'ges_procs','ges_ress','ges_locks','ges_cache_ress','ges_reg_msgs',
   'ges_big_msgs','ges_rsv_msgs','gcs_resources','dml_locks','max_shared_servers')
order by resource_name, inst_id
/

spool off
clear col
set termout on
set trimout off
set trimspool off
clear breaks