mark 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 i.owner, a.index_name, a.table_name, c.constraint_name, a.monitoring, a.used, a.start_monitoring, ROUND (SUM (b.bytes) / 1024 / 1024 / 1024) GB, ROUND (SUM (b.bytes) / 1024 / 1024) MB, i.tablespace_name FROM all_object_usage a, dba_segments b, dba_indexes i, dba_constraints c WHERE a.index_name = b.segment_name AND a.used = ‘NO’ AND b.segment_name = i.index_name AND a.table_name = c.table_name(+) GROUP BY i.owner, … Read more
The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10 g , this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the db file sequential read wait event. The Oracle process wants a block … 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
How to find the average active sessions between 2 dates. SELECT rollup_timestamp, average, minimum, maximum FROM mgmt$metric_daily WHERE target_name = ‘TRCOPDOV’ AND metric_label = ‘Throughput’ AND column_label = ‘Average Active Sessions’ AND rollup_timestamp BETWEEN TO_DATE (’01/11/2015′, ‘dd/mm/yyyy’) AND TO_DATE (’01/12/2015′, ‘dd/mm/yyyy’) ORDER BY 1;
How to resolve close waits from the OS Lets look at the close waits and see how many and the offending port. The following will show us the port and IPof the close wait netstat -an |grep -i close tcp 1 0 icudbaup02-priv.xxx.:53494 icudbaup01-priv.xxx.:61020 CLOSE_WAIT tcp 1 0 icudbaup02-priv.xxx.:53447 icudbaup01-priv.xxx.:61020 CLOSE_WAIT tcp 1 0 icudbaup02-priv.xxx.:53451 … Read more
SELECT t.target_name, t.target_type, el.severity, DECODE (el.open_status, 1, ‘Open’, ‘Closed’), el.msg, el.creation_date, el.incident_num, i.ticket_id FROM mgmt$events_latest el, mgmt$target t, mgmt$incidents i WHERE el.target_guid = t.target_guid AND I.INCIDENT_ID = el.incident_id AND t.target_type IN (‘oracle_listener’, ‘oracle_database’, ‘rac_database’, ‘host’) — AND el.open_status = 1 AND TRUNC (el.creation_date) >= SYSDATE – 7 AND i.ticket_id IS NOT NULL ORDER BY 4 … Read more
The MGMT$BLACKOUT_HISTORY file contains an overview of all finished blackouts for each target. To view the number of targets blacked out in the last 30 days, enter the following query: SELECT target_type, COUNT (*) cnt FROM mgmt$blackout_history WHERE start_time > SYSDATE – 30 GROUP BY target_type ;