Rem Rem $Header: db_outofbox_storage_gc.sql 22-sep-2006.03:21:00 denath Exp $ Rem Rem db_outofbox_storage_gc.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem db_outofbox_storage_gc.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem !!! Please update upgrade script if updates are made. !!! Rem Rem MODIFIED (MM/DD/YY) Rem denath 09/22/06 - Fix Bug 5455205.Changed query for Rem DBGroupCurrDBSpcUsage, Rem DBGroupHistStorageChartSql, Rem DBGroupTbspAllocUsage, Rem CurrDBGroupTbspFullAlertSummary, Rem DBGroupFullAlertCount. Rem SET DEFINE OFF DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_param_classes MGMT_IP_PARAM_CLASSES; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_targets MGMT_IP_TARGET_LIST; l_report_guid RAW(16); l_element_guid RAW(16); l_sql VARCHAR2(32767); BEGIN -- ------------------------------------------------------------------------- -- Report Definitions -- ------------------------------------------------------------------------- -- ------------------------------------------------------------------------- -- REPORT 1: Database Space Usage (Group) -- -- Description: This report will contains a summary of database space usage, -- for the specified group. It shows space usage for the entire -- group and then a breakdown by database. -- This also contains a summary of the number of Tablespace Full -- alerts for the group, and a breakdown by database. -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 1.0 Report Definition -- ------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'composite'; -- Add Time Picker to the Report -- Note: -- 0:0 Last 24 Hours -- 0:1 Last 7 Days : This Report Default -- 0:2 Last 31 Days l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:2'); l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_DATABASE_GROUP_SPACE_USAGE', p_description_nlsid => 'DBMSG_DATABASE_GROUP_SPACE_USAGE_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'DBMSG_DATABASE', p_sub_category_nlsid => 'DBMSG_SPACE_USAGE', p_late_binding_target_types => l_target_types, p_parameters => l_param_values); -- ---------------------------------------------------------------- -- Element 1.1: Database Space Usage (Group) Instruction Text -- -- Data Scope: Message Bundle -- Chart Type: Text -- Agent Version: All -- -- Description: Instructions for this report -- ---------------------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.reports.StorageReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DB_GROUP_SPACE_USAGE_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => '', p_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.2: Current Cumulative Space Usage -- -- Data Scope: Repository (Latest Data) -- Chart Type: Label Value Pair -- Agent Version: All -- -- Description: Shows 3 label/value pairs: -- Total Allocated Size (GB) -- Total Allocated Used (GB) -- Total Allocated Free (GB) -- ---------------------------------------------------------------- -- --------------------------------- -- 1.2 Register SQL for this element -- --------------------------------- l_sql := 'SELECT round(sum(t.tablespace_size/1024/1024/1024),2) AS ALLOCATED_GB, round(sum(t.tablespace_used_size/1024/1024/1024),2) AS USED_GB, round(sum((t.tablespace_size - t.tablespace_used_size)/1024/1024/1024),2) AS ALLOCATED_FREE_SPACE_GB FROM mgmt$db_tablespaces t, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE t.target_guid=db.target_guid'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupCurrDBSpcUsage', p_sql_statement => l_sql); -- --------------------------------- -- 1.2 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(4); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.reports.StorageReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBGroupCurrDBSpcUsage'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE', p_header_nlsid => 'DBMSG_CURRENT_DATABASE_GROUP_SPACE_USAGE', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.3: Historical Cumulative Space Usage -- -- -- Data Scope: Repository (Metric Data) -- Chart Type: Stacked Line Chart -- Agent Version: historical metric was added in 10.2 GC -- may be back ported to 10.1.0.5 -- -- Description: Shows all available history for 2 values: -- Total Allocated Size (GB) <-- Free (GB) line -- Total Allocated Used (GB) <-- Used (GB) line -- ---------------------------------------------------------------- -- --------------------------------- -- 1.3 Register SQL for this element -- --------------------------------- l_sql := 'SELECT decode(m.metric_column, ''spaceAllocated'', ''ALLOCATED_GB'', ''spaceUsed'', ''USED_GB''), m.rollup_timestamp AS rollup_timestamp, round(sum(m.average/1024),2) AS value FROM mgmt$metric_daily m, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE m.target_guid=db.target_guid AND m.metric_name=''tbspAllocation'' AND (m.metric_column=''spaceAllocated'' OR m.metric_column=''spaceUsed'') AND m.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND m.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY m.metric_column, m.rollup_timestamp'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupHistStorageChartSql', p_sql_statement => l_sql); -- --------------------------------- -- 1.3 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(8); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.db.rsc.reports.StorageReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBGroupHistStorageChartSql'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'timeSeriesChart'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'SIZE_GB'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.adjustTimes', 'true'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_GROUP_USER_CHART_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE', p_header_nlsid => 'DBMSG_HISTORICAL_GROUP_CUMULATIVE_SPACE_USAGE_DESC', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.4: Space Usage by Database -- -- -- Data Scope: Repository (Latest and Historical Metric Data) -- Chart Type: Table -- 1) All cols available For 10.2 GC Agent -- 2) 10.1.0.4 DB Control Agent -- Since tbspAllocation is only available in 10.2GC -- the 2 Avg and 2 Max columns require the metric. -- 3) For 10.1.0.4 agents that do not have DB Control, -- The used size is not available, so hide -- the Current Allocated Used, Current Allocated Free and -- Current Allocated Used (%) cols. -- NOTE: test the oracle_database.xml meta version for this -- -- Description: This table will report on every databsae in -- the group. Historical columns will be -- based on all historical data available in the -- database. -- -- Historical Default: Last 7 Days -- ---------------------------------------------------------------- -- --------------------------------- -- 1.4 Register SQL for this element -- --------------------------------- l_sql := 'SELECT curr.db_name AS DBNAME, round(curr.curr_alloc_size_gb,2) AS SIZE_GB, round(curr.curr_alloc_used_gb,2) AS USED_GB, round(curr.curr_alloc_free_gb,2) AS FREE_GB, round(curr.curr_alloc_used_pct,2) AS USED_PCT, round(tsize.avg_alloc_size_gb,2) AS SIZE_GB, round(usize.avg_alloc_used_gb,2) AS USED_GB, round((tsize.avg_alloc_size_gb - usize.avg_alloc_used_gb),2) AS FREE_GB, round(((usize.avg_alloc_used_gb*100)/ decode(tsize.avg_alloc_size_gb,0,1,tsize.avg_alloc_size_gb)) ,2) AS USED_PCT, round(tsize.max_alloc_size_gb,2) AS SIZE_GB, round(usize.max_alloc_used_gb,2) AS USED_GB, round((tsize.max_alloc_size_gb - usize.max_alloc_used_gb),2) AS FREE_GB, round(((usize.max_alloc_used_gb*100)/ decode(tsize.max_alloc_size_gb,0,1,tsize.max_alloc_size_gb)) ,2) AS USED_PCT, round(tsize.min_alloc_size_gb,2) AS SIZE_GB, round(usize.min_alloc_used_gb,2) AS USED_GB, round((tsize.min_alloc_size_gb - usize.min_alloc_used_gb),2) AS FREE_GB, round(((usize.min_alloc_used_gb*100)/ decode(tsize.min_alloc_size_gb,0,1,tsize.max_alloc_size_gb)) ,2) AS USED_PCT FROM (SELECT dbs.member_target_guid AS target_guid, max(dbs.member_target_name) AS db_name, sum(t.tablespace_size/1024/1024/1024) AS curr_alloc_size_gb, sum(t.tablespace_used_size/1024/1024/1024) AS curr_alloc_used_gb, sum((t.tablespace_size - t.tablespace_used_size)/1024/1024/1024) AS curr_alloc_free_gb, (sum(t.tablespace_used_size) * 100)/sum(t.tablespace_size) AS curr_alloc_used_pct FROM mgmt$group_flat_memberships dbs, mgmt$db_tablespaces t WHERE dbs.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (dbs.member_target_type=''rac_database'' OR (dbs.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target tg WHERE tg.target_guid=dbs.member_target_guid AND tg.type_qualifier3 != ''RACINST'' ) )) AND t.target_guid=dbs.member_target_guid GROUP BY dbs.member_target_guid ) curr, (SELECT target_guid AS target_guid, avg(sum_avg_alloc_size_gb) AS avg_alloc_size_gb, max(sum_max_alloc_size_gb) AS max_alloc_size_gb, max(sum_min_alloc_size_gb) AS min_alloc_size_gb FROM (SELECT dbs.member_target_guid AS target_guid, sum(md.average)/1024 AS sum_avg_alloc_size_gb, sum(md.maximum)/1024 AS sum_max_alloc_size_gb, sum(md.minimum)/1024 AS sum_min_alloc_size_gb FROM mgmt$group_flat_memberships dbs, mgmt$metric_daily md WHERE dbs.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (dbs.member_target_type=''rac_database'' OR (dbs.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target tg WHERE tg.target_guid=dbs.member_target_guid AND tg.type_qualifier3 != ''RACINST'' ) )) AND md.target_guid = dbs.member_target_guid AND md.metric_name=''tbspAllocation'' AND md.metric_column=''spaceAllocated'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY dbs.member_target_guid, md.rollup_timestamp) GROUP BY target_guid ) tsize, (SELECT target_guid AS target_guid, avg(sum_avg_alloc_used_gb) AS avg_alloc_used_gb, max(sum_max_alloc_used_gb) AS max_alloc_used_gb, max(sum_min_alloc_used_gb) AS min_alloc_used_gb FROM (SELECT dbs.member_target_guid AS target_guid, sum(md.average)/1024 AS sum_avg_alloc_used_gb, sum(md.maximum)/1024 AS sum_max_alloc_used_gb, sum(md.minimum)/1024 AS sum_min_alloc_used_gb FROM mgmt$group_flat_memberships dbs, mgmt$metric_daily md WHERE dbs.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (dbs.member_target_type=''rac_database'' OR (dbs.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target tg WHERE tg.target_guid=dbs.member_target_guid AND tg.type_qualifier3 != ''RACINST'' ) )) AND md.target_guid = dbs.member_target_guid AND md.metric_name=''tbspAllocation'' AND md.metric_column=''spaceUsed'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY dbs.member_target_guid, md.rollup_timestamp) GROUP BY target_guid ) usize WHERE tsize.target_guid=curr.target_guid AND usize.target_guid=curr.target_guid'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupTbspAllocUsage', p_sql_statement => l_sql); -- --------------------------------- -- 1.4 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(19); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stor.TablespaceMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBGroupTbspAllocUsage'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.emo.util.reports.storage.reportElement', 'DATABASE_GROUP_TBLSPC_ALLOCATION_SPACE_USAGE'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'CURRENT_HDR'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '1'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '4'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'AVERAGE_HDR'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '5'); l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '8'); l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader3', 'MAXIMUM_HDR'); l_param_values(14) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol3', '9'); l_param_values(15) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol3', '12'); l_param_values(16) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader4', 'MINIMUM_HDR'); l_param_values(17) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol4', '13'); l_param_values(18) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol4', '16'); l_param_values(19) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.adjustTimes', 'true'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE', p_header_nlsid => 'DBMSG_SPACE_USAGE_BY_DB_DESC', p_element_order => 4, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 1.5: Separator -- -- Data Scope: N/A -- Chart Type: Separator -- Agent Version: N/A -- -- Description: Separate space usage info from alert info -- ------------------------------------------------------------ l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_SEPARATOR', p_element_type_nlsid => 'IPMSG_NO_TARGET_TYPE', p_header_nlsid => '', p_element_order => 5, p_element_row => 4, p_parameters => null, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.6: Database Space Usage (Group) Alert Instruction Text -- -- Data Scope: Message Bundle -- Chart Type: Text -- Agent Version: All -- -- Description: Instructions for this report -- ---------------------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.reports.StorageReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DB_GROUP_SPACE_USAGE_TBSPFULL_ALERT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => '', p_element_order => 6, p_element_row => 5, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.7: Outstanding Cumulative Tablespace Full Alerts -- -- Data Scope: Message Bundle -- Chart Type: Name/Value pairs -- Agent Version: bytes free was supported in 10.2, but is being -- backported to 10.1.0.5 -- Description: Space Used (%) Critical and Warning alert counts -- Free Space (MB) Critical and Warning alert counts -- ---------------------------------------------------------------- -- --------------------------------- -- 1.7 Register SQL for this element -- --------------------------------- l_sql := 'SELECT nvl(pctUsed.critical,0) AS USED_PERCENT, decode(NVL(pctUsed.critical,0),0,15, 25), nvl(pctUsed.warning,0) AS EMPTY, decode(NVL(pctUsed.warning,0),0,15, 20), nvl(bytesFree.critical,0) AS FREE_BYTES, decode(NVL(bytesFree.critical,0),0,15, 25), nvl(bytesFree.warning,0) AS EMPTY, decode(NVL(bytesFree.warning,0),0,15, 20) FROM (SELECT sum(decode(alert_state, ''Critical'', count(1))) AS critical, sum(decode(alert_state, ''Warning'', count(1))) AS warning FROM mgmt$alert_current ac, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE gm.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE ac.target_guid=db.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' GROUP BY alert_state) pctUsed, (SELECT sum(decode(alert_state, ''Critical'', count(1))) AS critical, sum(decode(alert_state, ''Warning'', count(1))) AS warning FROM mgmt$alert_current ac, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE gm.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE ac.target_guid=db.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' GROUP BY alert_state) bytesFree'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.CurrDBGroupTbspFullAlertSummary', p_sql_statement => l_sql); -- --------------------------------- -- 1.7 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.reports.ReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.CurrDBGroupTbspFullAlertSummary'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', 'USED_PERCENT'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE', p_header_nlsid => 'DBMSG_CURRENT_DATABASE_GROUP_TBSPFULL_ALERT_SUMMARY_DESC', p_element_order => 7, p_element_row => 6, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------------------- -- Element 1.8: Outstanding Tablespace Full Alerts by Database -- -- Data Scope: Message Bundle -- Chart Type: Table -- Agent Version: bytes free was supported in 10.2, but is being -- backported to 10.1.0.5 -- Description: For each database: -- Space Used (%) Critical and Warning alert counts -- Free Space (MB) Critical and Warning alert counts -- ---------------------------------------------------------------- -- --------------------------------- -- 1.8 Register SQL for this element -- --------------------------------- l_sql := 'SELECT pctUsed.database AS DBNAME, nvl(pctUsed.critical,0) AS CRITICAL, nvl(pctUsed.warning,0) AS WARNING, nvl(bytesFree.critical,0) AS CRITICAL, nvl(bytesFree.warning,0) AS WARNING FROM (SELECT target_guid AS target_guid, target_name AS database, sum(critical) AS critical, sum(warning) AS warning FROM (SELECT ac.target_guid AS target_guid, ac.target_name AS target_name, decode(ac.alert_state, ''Critical'', count(1)) AS critical, decode(ac.alert_state, ''Warning'', count(1)) AS warning FROM mgmt$alert_current ac, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE gm.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE ac.target_guid=db.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' GROUP BY ac.target_guid, ac.target_name, ac.alert_state) GROUP BY target_guid,target_name) pctUsed, (SELECT target_guid AS target_guid, target_name AS database, sum(critical) AS critical, sum(warning) AS warning FROM (SELECT ac.target_guid AS target_guid, ac.target_name AS target_name, decode(ac.alert_state, ''Critical'', count(1)) AS critical, decode(ac.alert_state, ''Warning'', count(1)) AS warning FROM mgmt$alert_current ac, (SELECT gm.member_target_guid as target_guid FROM mgmt$group_flat_memberships gm WHERE gm.composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND 1 = (SELECT 1 FROM mgmt$target t WHERE t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST'' ) )) ) db WHERE ac.target_guid=db.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' GROUP BY ac.target_guid, ac.target_name, ac.alert_state) GROUP BY target_guid, target_name) bytesFree WHERE bytesFree.target_guid(+)=pctUsed.target_guid'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupFullAlertCount', p_sql_statement => l_sql); -- --------------------------------- -- 1.8 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(11); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stor.TablespaceMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBGroupFullAlertCount'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'SPACE_USED_ALERT_COUNT'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '1'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '2'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'FREE_SPACE_ALERT_COUNT'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '3'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '4'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE', p_header_nlsid => 'DBMSG_DATABASE_GROUP_TBSPFULL_ALERT_COUNT_DESC', p_element_order => 8, p_element_row => 6, p_parameters => l_param_values, p_targets => null); COMMIT; END; / SET DEFINE ON