How to reclaim space from system Solution Step 1. Check to see what is the lagest table in the system tablespace SELECT * FROM ( SELECT OWNER, SEGMENT_NAME, BYTES / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = ‘TABLE’ ORDER BY BYTES / 1024 / 1024 DESC) WHERE ROWNUM
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 * FROM all_views WHERE view_name LIKE ‘%USAGE%’ select a.index_name, (s.bytes/1024/1024/1024) from all_object_usage a, dba_segments s where a.index_name=s.segment_name and a.used=’NO’ group by a.index_name; SELECT SUM (ROUND (bytes / 1024 / 1024 / 1024)) AS GB FROM dba_segments WHERE segment_name IN (SELECT index_name FROM all_object_usage WHERE used = ‘NO’); SELECT * FROM gisdba.dba_object_usage; SELECT (bytes / … Read more
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;