@@header set term off /* * * Author : Vishal Gupta * Purpose : Display Tablespace usage * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 26-Jan-15 Vishal Gupta Renamed oper_count heading to "Resize Oper Count" * 28-MAY-12 Vishal Gupta Fixed to differentiate between instance specific * and generic (*.param) parameter values in spfile * 14-MAR-12 Vishal Gupta Modified to make it RAC friendly * 15-SEP-08 Vishal Gupta First Draft * */ set term on COLUMN inst_id HEADING "Inst#" FORMAT 99999 COLUMN name HEADING "Name" FORMAT a21 COLUMN current_value1 HEADING "Current" FORMAT a10 COLUMN spfile_value1 HEADING "SPFile" FORMAT a10 COLUMN current_value2 HEADING "Current" FORMAT a10 COLUMN spfile_value2 HEADING "SPFile" FORMAT a10 COLUMN current_value3 HEADING "Current" FORMAT a10 COLUMN spfile_value3 HEADING "SPFile" FORMAT a10 COLUMN current_value4 HEADING "Current" FORMAT a10 COLUMN spfile_value4 HEADING "SPFile" FORMAT a10 COLUMN current_value5 HEADING "Current" FORMAT a10 COLUMN spfile_value5 HEADING "SPFile" FORMAT a10 COLUMN current_value6 HEADING "Current" FORMAT a10 COLUMN spfile_value6 HEADING "SPFile" FORMAT a10 COLUMN current_value7 HEADING "Current" FORMAT a10 COLUMN spfile_value7 HEADING "SPFile" FORMAT a10 COLUMN current_value8 HEADING "Current" FORMAT a10 COLUMN spfile_value8 HEADING "SPFile" FORMAT a10 COLUMN component HEADING "Component" FORMAT a24 COLUMN user_specified_size HEADING "User|Specified|(MB)" FORMAT 99,999,999 COLUMN current_size HEADING "Current|(MB)" FORMAT 99,999,999 COLUMN free_size HEADING "Free|(MB)" FORMAT 99,999,999 COLUMN min_size HEADING "Min|(MB)" FORMAT 99,999,999 COLUMN max_size HEADING "Max|(MB)" FORMAT 99,999,999 COLUMN GRANULE_SIZE HEADING "Granule|(MB)" FORMAT 9,999 COLUMN last_oper_type HEADING "Last|Operation|Type" FORMAT a12 COLUMN oper_count HEADING "Resize|Oper|Count" FORMAT 99,999,999 COLUMN last_oper_time HEADING "Last|Operation|Time" FORMAT a18 BREAK ON REPORT COMPUTE SUM LABEL 'Total' OF current_size FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF free_size FORMAT 99,999,999 ON REPORT PROMPT PROMPT #################################################################### PROMPT ####### ####### PROMPT ####### Automatic Shared Memory Management Settings ####### PROMPT ####### ####### PROMPT #################################################################### PROMPT PROMPT - <---- Instance 1 ---> <---- Instance 2 ---> <---- Instance 3 ---> <---- Instance 4 ---> <---- Instance 5 ---> <---- Instance 6 ---> <---- Instance 7 ---> <---- Instance 8 ---> select RPAD(pp.name,20) || '|' name , MAX(DECODE(pp.inst_id, 1, pp.display_value,NULL)) current_value1 , MAX(DECODE(pp.inst_id, 1, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value1 , MAX(DECODE(pp.inst_id, 2, pp.display_value,NULL)) current_value2 , MAX(DECODE(pp.inst_id, 2, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value2 , MAX(DECODE(pp.inst_id, 3, pp.display_value,NULL)) current_value3 , MAX(DECODE(pp.inst_id, 3, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value3 , MAX(DECODE(pp.inst_id, 4, pp.display_value,NULL)) current_value4 , MAX(DECODE(pp.inst_id, 4, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value4 , MAX(DECODE(pp.inst_id, 5, pp.display_value,NULL)) current_value5 , MAX(DECODE(pp.inst_id, 5, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value5 , MAX(DECODE(pp.inst_id, 6, pp.display_value,NULL)) current_value6 , MAX(DECODE(pp.inst_id, 6, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value6 , MAX(DECODE(pp.inst_id, 7, pp.display_value,NULL)) current_value7 , MAX(DECODE(pp.inst_id, 7, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value7 , MAX(DECODE(pp.inst_id, 8, pp.display_value,NULL)) current_value8 , MAX(DECODE(pp.inst_id, 8, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value8 from gv$system_parameter pp JOIN gv$instance i ON pp.inst_id = i.inst_id LEFT OUTER JOIN gv$spparameter sp ON pp.inst_id = sp.inst_id AND sp.sid = i.instance_name and pp.name = sp.name LEFT OUTER JOIN gv$spparameter sp_generic ON sp_generic.inst_id = pp.inst_id AND sp_generic.sid = '*' and sp_generic.name = pp.name WHERE ( pp.name in ('memory_target' ,'memory_max_target' ,'sga_target' ,'sga_max_size' ,'lock_sga' ,'pre_page_sga' ,'pga_aggregate_target' ,'large_pool_size' ,'use_large_pages' ) ) GROUP BY pp.name UNION ALL -- Get Host physical memory select RPAD(os.stat_name,20) || '|' name , MAX(DECODE(os.inst_id, 1, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value1 , MAX(DECODE(os.inst_id, 1, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value1 , MAX(DECODE(os.inst_id, 2, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value2 , MAX(DECODE(os.inst_id, 2, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value2 , MAX(DECODE(os.inst_id, 3, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value3 , MAX(DECODE(os.inst_id, 3, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value3 , MAX(DECODE(os.inst_id, 4, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value4 , MAX(DECODE(os.inst_id, 4, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value4 , MAX(DECODE(os.inst_id, 5, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value5 , MAX(DECODE(os.inst_id, 5, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value5 , MAX(DECODE(os.inst_id, 6, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value6 , MAX(DECODE(os.inst_id, 6, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value6 , MAX(DECODE(os.inst_id, 7, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value7 , MAX(DECODE(os.inst_id, 7, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value7 , MAX(DECODE(os.inst_id, 8, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value8 , MAX(DECODE(os.inst_id, 8, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value8 from gv$osstat os where os.stat_name = 'PHYSICAL_MEMORY_BYTES' GROUP BY os.stat_name ORDER BY 1 / BREAK ON inst_id SKIP 1 SELECT c.inst_id , c.component , ROUND(c.user_specified_size / 1024 / 1024) user_specified_size , ROUND(c.current_size / 1024 / 1024) current_size , ROUND(NVL(s.bytes,0) / 1024 / 1024) free_size , ROUND(c.min_size / 1024 / 1024) min_size , ROUND(c.max_size / 1024 / 1024) max_size , ROUND(c.GRANULE_SIZE / 1024 / 1024) GRANULE_SIZE , c.oper_count , c.last_oper_type , to_char(c.last_oper_time,'DD-MON-YY hh24:mi:Ss') last_oper_time FROM gv$sga_dynamic_components c , GV$SGASTAT s WHERE c.inst_id = s.inst_id (+) AND c.component = s.pool (+) AND s.name (+) = 'free memory' AND c.current_size <> 0 ORDER BY c.inst_id , c.component / @@footer