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
SELECT DISTINCT st.target_name, st.mountpoint, ROUND ( (freeb / 1073741824), 2) AS “Free (GB)”, ROUND ( (sizeb / 1073741824), 2) AS “Size (GB)”, ROUND ( ( ( (sizeb – freeb) / sizeb) * 100), 2) AS “Used (%)” FROM MGMT$STORAGE_REPORT_LOCALFS st, mgmt$os_summary os WHERE st.target_name = os.HOST AND st.mountpoint = ‘/usr/local/oracle’ OR st.mountpoint LIKE ‘/databases%’ OR … Read more
alter session set nls_date_format=’dd/mm/rrrr hh:mi:ss’ SELECT a.incident_id, b.creation_date, b.last_updated_date, b.ticket_id, b.summary_msg, c.target_name, DECODE (a.open_status, ‘1’, ‘Open’, ‘Closed’) FROM mgmt$INCIDENT_TARGET a, mgmt$incidents b, mgmt$target c WHERE a.incident_id = b.incident_id AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND LOWER (b.SUMMARY_MSG) LIKE ‘%filesystem%’ AND c.target_type = ‘host’ AND a.open_status = 1 ORDER BY b.last_updated_date DESC