Capacity and Growth Forecast How to estimate the growth and forecast of a database on a host. Step-by-step guide The procedure is at the bottom of this article. Login to enterprise manager from sqlplus (from omsdbaup03) oracle@omsdbaup03:/users/oracle [emrep12c] $ sqlplus sysman/xxxxxx 2. Follow the following to calculate growth for each database on a host SQL> … Read more
select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE , t.NAME “Tablespace”, s.growth/(1024*1024) “Growth in MB”, (SELECT sum(bytes)/(1024*1024*1024) FROM dba_segments WHERE segment_name=o.object_name) “Total Size(GB)” FROM DBA_OBJECTS o, ( SELECT TS#,OBJ#, SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT GROUP BY TS#,OBJ# HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s, v$tablespace t WHERE s.OBJ# = o.OBJECT_ID … Read more
SELECT a.owner, a.index_name, a.table_name, a.monitoring, a.used, a.start_monitoring, SUM (b.bytes) / 1024 / 1024 / 1024 GB FROM all_object_usage a, dba_segments b WHERE a.index_name = b.segment_name AND a.used = ‘NO’ GROUP BY a.owner, a.index_name, a.table_name, a.monitoring, a.used, a.start_monitoring ORDER BY GB DESC;
SELECT idx.index_name, SUM(bytes) FROM dba_segments seg, dba_indexes idx WHERE idx.table_owner = ‘TRANSSENDOPS_PRD’ AND idx.table_name in (select * from all_object_usage) AND idx.owner = seg.owner AND idx.index_name = seg.segment_name GROUP BY idx.index_name;
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