REM $Id: db_trend.sql,v 1.6 2013/06/14 22:11:55 ewan Exp $ column target_name format a30 new_value def_t column target_type format a20 select target_name, target_type from mgmt$target where target_type='rac_database' or (target_type='oracle_database' and type_qualifier3 != 'RACINST') order by lower(target_name) / set verify off accept db prompt "db [&def_t]> " default '&def_t' column target_guid new_value t_guid select target_guid from mgmt_targets where target_name='&db'; accept res prompt "aggregation (y, m or d) [d]> " default 'd' column format new_value form select case when '&res'='y' then 'yyyy' when '&res'='m' then 'yyyy-mm' else 'yyyy-mm-dd' end as format from dual / alter session set nls_date_format='&form'; column dstart new_value def_d1 column dfinish new_value def_d2 select current_date - interval '12' month as dstart, current_date as dfinish from dual / accept start prompt "start (&form) [&def_d1]> " default '&def_d1' accept finish prompt "finish (&form) [&def_d2]> " default '&def_d2' set linesize 80 pagesize 0 feedback off set numformat 99999999 column tablespace format a32 define out=db_trend_&res._&db._&start._&finish spool &out..txt select to_date(to_char(alloc.timestamp,'&form'),'&form') as stamp, round(sum(alloc.avg_size_mb),2)/1024 as avg_size_gb, round(sum(used.avg_used_mb),2)/1024 as avg_used_gb, round(sum(alloc.avg_size_mb - used.avg_used_mb),2)/1024 as avg_free_gb from ( select m.key_value as tablespace, to_date(to_char(m.rollup_timestamp,'&form'),'&form') as timestamp, avg(m.average) as avg_size_mb, min(m.minimum) as min_size_mb, max(m.maximum) as max_size_mb from mgmt$metric_daily m inner join mgmt$target_type t on m.target_guid=t.target_guid and m.metric_guid=t.metric_guid where t.target_guid=hextoraw('&t_guid') and ( t.target_type='rac_database' or (t.target_type='oracle_database' and t.type_qualifier3 != 'RACINST') ) and t.metric_name='tbspAllocation' and t.metric_column='spaceAllocated' and m.rollup_timestamp >= to_date('&start') and m.rollup_timestamp <= to_date('&finish') group by to_date(to_char(m.rollup_timestamp,'&form'),'&form'), m.key_value ) alloc inner join ( select m.key_value as tablespace, to_date(to_char(m.rollup_timestamp,'&form'),'&form') as timestamp, avg(m.average) as avg_used_mb, min(m.minimum) as min_used_mb, max(m.maximum) as max_used_mb from mgmt$metric_daily m inner join mgmt$target_type t on m.target_guid=t.target_guid and m.metric_guid=t.metric_guid where t.target_guid=hextoraw('&t_guid') and ( t.target_type='rac_database' or (t.target_type='oracle_database' and t.type_qualifier3 != 'RACINST') ) and t.metric_name='tbspAllocation' and t.metric_column='spaceUsed' and m.rollup_timestamp >= to_date('&start') and m.rollup_timestamp <= to_date('&finish') group by to_date(to_char(m.rollup_timestamp,'&form'),'&form'), m.key_value ) used on alloc.timestamp=used.timestamp and alloc.tablespace=used.tablespace group by to_char(alloc.timestamp,'&form') order by to_char(alloc.timestamp,'&form') / spool off spool &out..gp prompt set datafile separator whitespace prompt set terminal pdfcairo prompt set output "&out..pdf" prompt set term pdfcairo enhanced font "Arial, 8" prompt prompt set title "Database space history for &db" prompt prompt set xlabel "Date" prompt set xdata time prompt set timefmt "%Y-%m-%d" prompt set xtics format "%m/%Y" prompt prompt set ylabel "GiB" prompt prompt set pointsize 0.4 prompt set style data linespoints prompt prompt plot "&out..txt" using 1:2 title "Allocated", \ prompt "&out..txt" using 1:3 title "Used", \ prompt "&out..txt" using 1:4 title "Free" spool off set pagesize 14 verify on feedback on