Enterprise Manager

select target_name, max(rollup_timestamp) from mgmt$metric_daily where target_TYPE=’rac_database’ and METRIC_NAME=’DATABASE_SIZE’ and column_label like ‘Used%’ group by target_name;

with my_data AS ( select key_value2, ROUND ((( (MAX (AVERAGE) – MIN (AVERAGE)))/1024/1024),2) as AVERAGE from mgmt$metric_daily where target_type = ‘osm_cluster’ and key_value like ‘DATA%’ and column_label = ‘Total Bytes’ and rollup_timestamp >= sysdate-182 group by key_value2 ) select * from my_data pivot ( AVG(AVERAGE) for key_value2 IN (‘ASM’, ‘EILPRD1’, ‘EILPRDDOV’, ‘EILPRDSUN’, ‘NEWDSS02DOV’, ‘NEWDSS03PRD’, ‘NEWDSSOAP01’, … Read more

with my_data AS ( select key_value2, rollup_timestamp, average from mgmt$metric_daily where target_type = ‘osm_cluster’ and key_value like ‘DATA%’ and column_label = ‘Total Bytes’ and rollup_timestamp >= sysdate-14 ) select * from my_data pivot ( sum(average/1024/1024/1024) for key_value2 IN (‘ASM’, ‘EILPRD1’, ‘EILPRDDOV’, ‘EILPRDSUN’, ‘NEWDSS02DOV’, ‘NEWDSS03PRD’, ‘NEWDSSOAP01’, ‘NEWDSSOAP04’, ‘NEWSOA02DOV’, ‘NEWSOAP01’, ‘NEWSOAP04’, ‘NEWTOLTTP01’, ‘ORCDBAUZ01’, ‘SBYDSS02DOV’, ‘SBYDSS02PRD’, ‘SBYDSS03PRD’, ‘SBYDSSOAP01’, … Read more

SELECT target_name, ROUND ( (MAX (AVERAGE) – MIN (AVERAGE)) / 14 / 1024 / 1024 / 1024/1024,2) AS GB FROM mgmt$metric_daily WHERE target_name in (select target_name from mgmt_targets where target_type=’osm_cluster’) AND key_value like ‘DATA%’ AND column_label = ‘Total Bytes’ AND TRUNC (rollup_timestamp) >= TRUNC (SYSDATE) – 14 GROUP BY target_name;

SET SERVEROUTPUT ON; DECLARE –Cursor Declaration CURSOR c1 IS SELECT SUM (ROUND ( (tablespace_usedsize * 8 * 1024) / 1024 / 1024, 2)) AS “used space”, snap_id FROM DBA_HIST_TBSPC_SPACE_USAGE WHERE snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE TO_CHAR (BEGIN_INTERVAL_TIME) BETWEEN ’16/10/2011%18%’ AND ’20/10/2011%18′) GROUP BY snap_id ORDER BY snap_id ASC; –Variable Declaretion row_counter NUMBER; db_used_size_1 … Read more

SELECT (max(round(avg(a_size.size_gb),2)) – min(round(avg(a_size.size_gb),2)))/12 FROM (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS size_gb FROM mgmt$metric_daily m, mgmt$target_type t WHERE t.target_guid=HEXTORAW(‘A06F734DEB35E1CFE38C9AB4729508E2′) AND (t.target_type=’rac_database’ OR (t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND m.target_guid=t.target_guid AND m.metric_guid=t.metric_guid AND t.metric_name=’tbspAllocation’ AND (t.metric_column=’spaceAllocated’) AND m.rollup_timestamp >= sysdate-365 AND m.rollup_timestamp <= sysdate GROUP BY m.metric_column, m.rollup_timestamp) a_size, (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS used_gb … Read more

Get the average growth over the past 12 months. Ensure you change the TARGET_GUID. SELECT (max(round(avg(a_size.size_gb),2)) – min(round(avg(a_size.size_gb),2)))/12 FROM (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS size_gb FROM mgmt$metric_daily m, mgmt$target_type t WHERE t.target_guid=HEXTORAW(‘A06F734DEB35E1CFE38C9AB4729508E2′) AND (t.target_type=’rac_database’ OR (t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND m.target_guid=t.target_guid AND m.metric_guid=t.metric_guid AND t.metric_name=’tbspAllocation’ AND (t.metric_column=’spaceAllocated’) AND m.rollup_timestamp >= sysdate-365 AND m.rollup_timestamp … Read more

SELECT b.tsname tablespace_name, MAX (b.used_size_mb) cur_used_size_mb, ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb FROM (SELECT a.days, a.tsname, used_size_mb, used_size_mb – LAG (used_size_mb, 1) OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days) inc_used_size_mb FROM ( SELECT TO_CHAR (sp.begin_interval_time, ‘MM-DD-YYYY’) days, ts.tsname, MAX ( ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024), 2)) used_size_mb FROM dba_hist_tbspc_space_usage tsu, dba_hist_tablespace_stat … Read more