Rem drv: Rem Rem $Header: db_outofbox_storage_issues.sql 18-jun-2007.11:05:24 mnihalan Exp $ Rem Rem db_outofbox_storage_issues.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem db_outofbox_storage_issues.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem !!! Please update upgrade script if updates are made. !!! Rem Rem MODIFIED (MM/DD/YY) Rem mnihalan 06/18/07 - Fix column order Rem mnihalan 06/14/07 - Fix Tablespace Free Space alerts Rem mnihalan 06/08/07 - Fix bug 6121888 Rem qsong 08/29/05 - 4574075 Rem rreilly 08/23/05 - bug 4497520 handle cleared message Rem qsong 08/22/05 - performance bug Rem rreilly 08/05/05 - bug 4497520 fix parse for server params vs ours Rem chyu 07/20/05 - modifying the type to post_creation Rem gsbhatia 07/11/05 - New repmgr header impl Rem mnihalan 05/17/05 - Fix reclaimbale space reports Rem mnihalan 05/13/05 - Fix rec space report Rem mnihalan 05/09/05 - Fix sorting on reclaimable space reports Rem rreilly 05/03/05 - Rem mnihalan 04/21/05 - Fix sorting on reclaimable space reports Rem mnihalan 04/14/05 - Fix sorting order Rem mnihalan 04/14/05 - Fix sorting column Rem rreilly 04/01/05 - bug 4262522 make sure db elems do not run for racinst Rem xshen 03/21/05 - Add comments Rem qsong 02/23/05 - swap instruction and separator position Rem mnihalan 02/17/05 - move segments with reclaimable space to Rem tablespace with reclaimable space Rem qsong 02/14/05 - Add instruction texts and split Tablespace Full Alerts report Rem mnihalan 01/14/05 - Change queries Rem mnihalan 01/04/05 - Fix bugs Rem mnihalan 12/13/04 - Fix translations Rem mnihalan 12/08/04 - Change message id Rem qsong 11/12/04 - qsong_asm_reports Rem qsong 10/29/04 - Rem mnihalan 10/22/04 - Fix sql Rem mnihalan 10/07/04 - Add reports Rem mnihalan 10/04/04 - Add sort columns Rem mnihalan 09/30/04 - Add elements and reports Rem mnihalan 09/27/04 - Created Rem SET DEFINE OFF BEGIN DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_targets MGMT_IP_TARGET_LIST; l_param_classes MGMT_IP_PARAM_CLASSES; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_report_guid RAW(16); l_element_guid RAW(16); l_sql VARCHAR2(32767); BEGIN -- ------------------------------------------------------------------------- -- REPORT 1: Tablespace Space Used (%) Alerts -- -- Description: This report will contain a list of outstanding alerts, -- a historical breakdown of space used(%) for tablespaces -- with outstanding alerts and a complete alert history. -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 1.0 Report Definition -- ------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(2); l_target_types(1) := 'oracle_database'; l_target_types(2) := 'rac_database'; -- 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:1'); l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_TABLESPACE_FULL_ALERT_BY_PERCENT', p_description_nlsid => 'DBMSG_TABLESPACE_FULL_ALERT_BY_PERCENT_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'DBMSG_DATABASE', p_sub_category_nlsid => 'DBMSG_SPACE_ISSUES', p_late_binding_target_types => l_target_types, p_parameters => l_param_values); -- ---------------------------------------------------------------- -- Element 1.1: Tablespace Space Used(%) Alerts 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_TBSP_USED_PCT_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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 1.2: Outstanding Alerts -- -- Data Scope: Repository (Current) -- Chart Type: Table -- Agent Version: All -- -- Description: Shows current outstanding alerts for the -- Tablespace Full Metric, Space Used(%) Metric -- column. -- NOTE: For Severity, 25 Critical; 20 Warning; -- ------------------------------------------------------------ -- --------------------------------- -- 1.2 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ac.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ac.key_value AS TABLESPACE, round(substr(ac.value_param, 1, (decode(instr(ac.value_param,'''' '''',1), 0, decode(instr(ac.value_param,''''&'''',-1), 0, length(ac.value_param),instr(ac.value_param,''''&'''',-1)), instr(ac.value_param,'''' '''',1)) -1) ),2) AS USED_PERCENT, ac.collection_timestamp as ALERT_TRIGGERED, round(sysdate - ac.collection_timestamp,2) AS DAYS_OPEN FROM (SELECT target_guid, metric_name, metric_column, key_value, alert_state, substr(message_params, instr(message_params,''''&'''',1)+1, length(message_params)-1) as value_param, collection_timestamp FROM mgmt$alert_current) ac, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND ac.target_guid=t.target_guid AND (ac.metric_name=''''problemTbsp'''' OR ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''pctUsed'''' ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspFullSpaceUsedPctOpenAlerts', 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(8); 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.TbspFullSpaceUsedPctOpenAlerts'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); 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_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_OUTSTANDING_ALERTS', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 1.3: Historical Tablespace Space Used(%) -- -- Data Scope: Repository (Historical) -- Chart Type: Table -- Agent Version: All -- -- Description: For Tablespaces with outstanding Space Used (%) -- alerts, this table shows the average, minimum, -- and max for Space Used(%). -- Sorting: Sort by Average Space Used (%) -- ------------------------------------------------------------ -- --------------------------------- -- 1.3 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT ac.key_value AS TABLESPACE, round(avg(md.average),2) AS AVERAGE, round(min(md.minimum),2) AS MINIMUM, round(max(md.maximum),2) AS MAXIMUM FROM mgmt$alert_current ac, mgmt$metric_daily md, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND (ac.target_type=''''oracle_database'''' OR ac.target_type=''''rac_database'''') AND md.target_guid = ac.target_guid AND (md.target_type=''''oracle_database'''' OR md.target_type=''''rac_database'''') AND t.target_guid = ac.target_guid AND (ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''pctUsed'''' AND md.target_guid = ac.target_guid AND md.metric_name = ac.metric_name AND md.metric_column = ac.metric_column AND md.key_value = ac.key_value AND md.rollup_timestamp >= :2 AND md.rollup_timestamp <= :3 GROUP BY ac.key_value UNION ALL SELECT ts.tsname AS TABLESPACE, round(avg((tsu.tablespace_usedsize / tsu.tablespace_maxsize)*100), 2) AS AVERAGE, round(min((tsu.tablespace_usedsize / tsu.tablespace_maxsize)*100), 2) AS MINIMUM, round(max((tsu.tablespace_usedsize / tsu.tablespace_maxsize)*100), 2) AS MAXIMUM FROM (SELECT UNIQUE ts#, tsname FROM sys.dba_hist_tablespace_stat) ts, dba_hist_tbspc_space_usage tsu, dba_tablespaces t, mgmt$alert_current ac WHERE ts.ts# = tsu.tablespace_id AND ts.tsname = t.tablespace_name AND ac.metric_name = ''''problemTbsp'''' AND ac.metric_column=''''pctUsed'''' AND ac.key_value = t.tablespace_name AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') >= :2) AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') <= :3) GROUP BY ts.tsname ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in,start_date_in,end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.HistoricTbspSpaceUsedPct', 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(9); 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.HistoricTbspSpaceUsedPct'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'USED_PERCENT'); 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', '3'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TBSP_FULL_BY_PERCENT_HISTORICAL', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 1.4: Historical Tablespace Average Space Used(%) -- -- Data Scope: Repository (Historical) -- Chart Type: Timeseries Chart -- Agent Version: All -- -- Description: For Tablespaces with outstanding Space Used (%) -- alerts, this chart will show all Historical -- Average Space Used(%). -- ------------------------------------------------------------ -- --------------------------------- -- 1.4 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := ''SELECT TABLESPACE, TIME, AVG_SPC_USED_PCT FROM (SELECT md.key_value AS TABLESPACE, md.rollup_timestamp AS TIME, round(md.average,2) AS AVG_SPC_USED_PCT FROM mgmt$alert_current ac, mgmt$metric_daily md, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND (ac.target_type=''''oracle_database'''' OR ac.target_type=''''rac_database'''') AND md.target_guid = ac.target_guid AND (md.target_type=''''oracle_database'''' OR md.target_type=''''rac_database'''') AND t.target_guid = ac.target_guid AND (ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''pctUsed'''' AND md.target_guid = ac.target_guid AND md.metric_name = ac.metric_name AND md.metric_column = ac.metric_column AND md.key_value = ac.key_value AND md.rollup_timestamp >= :2 AND md.rollup_timestamp <= :3 UNION ALL SELECT ts.tsname AS TABLESPACE, to_timestamp(rtime, ''''MM/DD/YYYY HH24:MI:SS'''') AS TIME, ROUND(((tsu.tablespace_usedsize / tsu.tablespace_maxsize)*100), 2) AS AVG_SPC_USED_PCT FROM (SELECT UNIQUE ts#, tsname FROM dba_hist_tablespace_stat) ts, dba_hist_tbspc_space_usage tsu, dba_tablespaces t, mgmt$alert_current ac WHERE ts.ts# = tsu.tablespace_id AND ts.tsname = t.tablespace_name AND ac.metric_name=''''problemTbsp'''' AND ac.metric_column=''''pctUsed'''' AND ac.key_value = t.tablespace_name AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') >= :2) AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') <= :3)) tsi ORDER BY TABLESPACE, TIME ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in, start_date_in, end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.HistoricAvgTbspSpaceUsedPct', 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(8); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.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.HistoricAvgTbspSpaceUsedPct'); 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.sqlStatementIsPlSql', 'true'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'USED_PERCENT'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.adjustTimes', 'true'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_CHART_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TBSP_FULL_BY_PERCENT_HISTORICAL_AVERAGE', p_element_order => 5, p_element_row => 4, 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 outstanding alert details from the more -- generic alert history section. -- ------------------------------------------------------------ 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 => 6, p_element_row => 5, p_parameters => null, p_targets => null); -- ------------------------------------------------------------ -- Element 1.6: Alert History -- -- Data Scope: Repository (Historical) -- Chart Type: Table -- Agent Version: All -- -- Description: All available Alert history for the Space Used(%) -- Alert. This shows progression from warn to critical -- to clear. This allows users to see how long alerts -- were in various states before problems were addressed. -- ------------------------------------------------------------ -- --------------------------------- -- 1.6 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ah.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ah.key_value AS TABLESPACE, substr(ah.value_param, 1, (decode(instr(ah.value_param,'''' '''',1), 0, decode(instr(ah.value_param,''''&'''',-1), 0, length(ah.value_param),instr(ah.value_param,''''&'''',-1)), instr(ah.value_param,'''' '''',1)) -1) ) AS USED_PERCENT, ah.collection_timestamp as ALERT_TRIGGERED, decode(ah.alert_duration, 0, NULL, round((ah.alert_duration/24),2)) AS DAYS_OPEN FROM (SELECT target_guid, metric_name, metric_column, key_value, alert_state, alert_duration, decode(alert_state, ''''Clear'''', '''' '''', substr(message_params, instr(message_params,''''&'''',1)+1, length(message_params)-1)) as value_param, collection_timestamp FROM mgmt$alert_history) ah, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ah.target_guid=:1 AND t.target_guid = ah.target_guid AND (ah.metric_name=''''problemTbsp'''' OR ah.metric_name=''''problemTbsp10iDct'''') AND ah.metric_column=''''pctUsed'''' AND ah.collection_timestamp >= :2 AND ah.collection_timestamp <= :3 ORDER BY ah.key_value ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.AlertHistoryTbspSpaceUsedPct', p_sql_statement => l_sql); -- --------------------------------- -- 1.6 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.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.AlertHistoryTbspSpaceUsedPct'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '3'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); 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_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_ALERT_HISTORY', p_element_order => 7, p_element_row => 6, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------------------- -- REPORT 2: Tablespace Free Space (MB) Alerts -- -- Description: This report will contain a list of outstanding alerts, -- a historical breakdown of free space(mb) for tablespaces -- with outstanding alerts and a complete alert history. -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 2.0 Report Definition -- ------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(2); l_target_types(1) := 'oracle_database'; l_target_types(2) := 'rac_database'; -- 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:1'); l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_TABLESPACE_FULL_ALERT_BY_MB', p_description_nlsid => 'DBMSG_TABLESPACE_FULL_ALERT_BY_MB_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'DBMSG_DATABASE', p_sub_category_nlsid => 'DBMSG_SPACE_ISSUES', p_late_binding_target_types => l_target_types, p_parameters => l_param_values); -- ---------------------------------------------------------------- -- Element 2.1: Tablespace Free Space (MB) Alerts 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_TBSP_FREE_MB_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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 2.2: Outstanding Alerts -- -- Data Scope: Repository (Current) -- Chart Type: Table -- Agent Version: All -- -- Description: Shows current outstanding alerts for the -- Tablespace Full Metric, Free Space (MB) Metric -- column. -- NOTE: For Severity, 25 Critical; 20 Warning; -- ------------------------------------------------------------ -- --------------------------------- -- 2.2 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ac.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ac.key_value AS TABLESPACE, round(substr(ac.value_param, 1, (decode(instr(ac.value_param,'''' '''',1), 0, decode(instr(ac.value_param,''''&'''',-1), 0, length(ac.value_param),instr(ac.value_param,''''&'''',-1)), instr(ac.value_param,'''' '''',1)) -1) ),2) AS FREE_BYTES, ac.collection_timestamp as ALERT_TRIGGERED, round(sysdate - ac.collection_timestamp,2) AS DAYS_OPEN FROM (SELECT target_guid, metric_name, metric_column, key_value, alert_state, substr(message_params, instr(message_params,''''&'''',1)+1, length(message_params)-1) as value_param, collection_timestamp FROM mgmt$alert_current) ac, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND ac.target_guid=t.target_guid AND (ac.metric_name=''''problemTbsp'''' OR ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''bytesFree'''' ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspFullFreeMBOpenAlerts', p_sql_statement => l_sql); -- --------------------------------- -- 2.2 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.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.TbspFullFreeMBOpenAlerts'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); 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_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_OUTSTANDING_ALERTS', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 2.3: Historical Tablespace Free Space (MB) -- -- Data Scope: Repository (Historical) -- Chart Type: Table -- Agent Version: All -- -- Description: For Tablespaces with outstanding Free Space (MB) -- alerts, this table shows the average, minimum, -- and max for Free Space (MB). -- Sorting: Sort by Average Free Space (MB) -- ------------------------------------------------------------ -- --------------------------------- -- 2.3 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT ac.key_value AS TABLESPACE, round(avg(md.average),2) AS AVERAGE, round(min(md.minimum),2) AS MINIMUM, round(max(md.maximum),2) AS MAXIMUM FROM mgmt$alert_current ac, mgmt$metric_daily md, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND (ac.target_type=''''oracle_database'''' OR ac.target_type=''''rac_database'''') AND md.target_guid = ac.target_guid AND (md.target_type=''''oracle_database'''' OR md.target_type=''''rac_database'''') AND t.target_guid = ac.target_guid AND (ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''bytesFree'''' AND md.target_guid = ac.target_guid AND md.metric_name = ac.metric_name AND md.metric_column = ac.metric_column AND md.key_value = ac.key_value AND md.rollup_timestamp >= :2 AND md.rollup_timestamp <= :3 GROUP BY ac.key_value UNION ALL SELECT ts.tsname AS TABLESPACE, round(avg((tablespace_maxsize - tablespace_usedsize)*t.block_size/1024/1024), 2) AS AVERAGE, round(min((tablespace_maxsize - tablespace_usedsize)*t.block_size/1024/1024), 2) AS MINIMUM, round(max((tablespace_maxsize - tablespace_usedsize)*t.block_size/1024/1024), 2) AS MAXIMUM FROM (SELECT UNIQUE ts#, tsname FROM sys.dba_hist_tablespace_stat) ts, dba_hist_tbspc_space_usage tsu, dba_tablespaces t, mgmt$alert_current ac WHERE ts.ts# = tsu.tablespace_id AND ts.tsname = t.tablespace_name AND ac.metric_name = ''''problemTbsp'''' AND ac.metric_column=''''bytesFree'''' AND ac.key_value = t.tablespace_name AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') >= :2) AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') <= :3) GROUP BY ts.tsname ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in,start_date_in,end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.HistoricTbspFreeMB', p_sql_statement => l_sql); -- --------------------------------- -- 2.3 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(9); 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.HistoricTbspFreeMB'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'FREE_BYTES'); 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', '3'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TBSP_FULL_BY_MB_HISTORICAL', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 2.4: Historical Tablespace Average Free Space (MB) -- -- Data Scope: Repository (Historical) -- Chart Type: Timeseries Chart -- Agent Version: All -- -- Description: For Tablespaces with outstanding Free Space (MB) -- alerts, this chart will show all Historical -- Average Free Space (MB). -- ------------------------------------------------------------ -- --------------------------------- -- 2.4 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT TABLESPACE, TIME, FREE_BYTES FROM (SELECT md.key_value AS TABLESPACE, md.rollup_timestamp AS TIME, round(md.average,2) AS FREE_BYTES FROM mgmt$alert_current ac, mgmt$metric_daily md, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND (ac.target_type=''''oracle_database'''' OR ac.target_type=''''rac_database'''') AND md.target_guid = ac.target_guid AND (md.target_type=''''oracle_database'''' OR md.target_type=''''rac_database'''') AND t.target_guid = ac.target_guid AND (ac.metric_name=''''problemTbsp10iDct'''') AND ac.metric_column=''''bytesFree'''' AND md.target_guid = ac.target_guid AND md.metric_name = ac.metric_name AND md.metric_column = ac.metric_column AND md.key_value = ac.key_value AND md.rollup_timestamp >= :2 AND md.rollup_timestamp <= :3 UNION ALL SELECT ts.tsname AS TABLESPACE, to_timestamp(rtime, ''''MM/DD/YYYY HH24:MI:SS'''') AS TIME, ROUND(((tsu.tablespace_maxsize - tsu.tablespace_usedsize)*t.block_size/1024/1024), 2) AS FREE_BYTES FROM (SELECT UNIQUE ts#, tsname FROM dba_hist_tablespace_stat) ts, dba_hist_tbspc_space_usage tsu, dba_tablespaces t, mgmt$alert_current ac WHERE ts.ts# = tsu.tablespace_id AND ts.tsname = t.tablespace_name AND ac.metric_name=''''problemTbsp'''' AND ac.metric_column=''''bytesFree'''' AND ac.key_value = t.tablespace_name AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') >= :2) AND (to_timestamp(tsu.rtime, ''''MM/DD/YYYY HH24:MI:SS'''') <= :3)) tsi ORDER BY TABLESPACE, TIME ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in, start_date_in, end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.HistoricAvgTbspFreeMB', p_sql_statement => l_sql); -- --------------------------------- -- 2.4 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.ReportsMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.HistoricAvgTbspFreeMB'); 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.sqlStatementIsPlSql', 'true'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'FREE_BYTES'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.adjustTimes', 'true'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_CHART_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TBSP_FULL_BY_MB_HISTORICAL_AVERAGE', p_element_order => 5, p_element_row => 4, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 2.5: Separator -- -- Data Scope: N/A -- Chart Type: Separator -- Agent Version: N/A -- -- Description: Separate outstanding alert details from the more -- generic alert history section. -- ------------------------------------------------------------ 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 => 6, p_element_row => 5, p_parameters => null, p_targets => null); -- ------------------------------------------------------------ -- Element 2.6: Alert History -- -- Data Scope: Repository (Historical) -- Chart Type: Table -- Agent Version: All -- -- Description: All available Alert history for the Free Space (MB) -- Alert. This shows progression from warn to critical -- to clear. This allows users to see how long alerts -- were in various states before problems were addressed. -- ------------------------------------------------------------ -- --------------------------------- -- 2.6 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ah.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ah.key_value AS TABLESPACE, substr(ah.value_param, 1, (decode(instr(ah.value_param,'''' '''',1), 0, decode(instr(ah.value_param,''''&'''',-1), 0, length(ah.value_param),instr(ah.value_param,''''&'''',-1)), instr(ah.value_param,'''' '''',1)) -1) ) AS FREE_BYTES, ah.collection_timestamp as ALERT_TRIGGERED, decode(ah.alert_duration, 0, NULL, round((ah.alert_duration/24),2)) AS DAYS_OPEN FROM (SELECT target_guid, metric_name, metric_column, key_value, alert_state, alert_duration, decode(alert_state, ''''Clear'''', '''' '''', substr(message_params, instr(message_params,''''&'''',1)+1, length(message_params)-1)) as value_param, collection_timestamp FROM mgmt$alert_history) ah, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ah.target_guid=:1 AND t.target_guid = ah.target_guid AND (ah.metric_name=''''problemTbsp'''' OR ah.metric_name=''''problemTbsp10iDct'''') AND ah.metric_column=''''bytesFree'''' AND ah.collection_timestamp >= :2 AND ah.collection_timestamp <= :3 ORDER BY ah.key_value ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,start_date_in,end_date_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.AlertHistoryTbspFreeMB', p_sql_statement => l_sql); -- --------------------------------- -- 2.6 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.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.AlertHistoryTbspFreeMB'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '3'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); 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_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_ALERT_HISTORY', p_element_order => 7, p_element_row => 6, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------------------- -- REPORT 3: Tablespaces with Problem Segments -- -- Description: Two tables, one for each column associated with the -- Tablespaces with Problem Segments metric: -- Tablespaces with Segments Approaching Max Extents -- Tablespaces with Segments Not Able to Extend -- -- These tables will show all open alerts for the specified -- metic column, along with a complete list of segments. This -- is the only place where this info appears together. -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 3.0 Report Definition -- ------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(2); l_target_types(1) := 'oracle_database'; l_target_types(2) := 'rac_database'; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_TABLESPACE_PROBLEM_SEGMENTS', p_description_nlsid => 'DBMSG_TABLESPACE_PROBLEM_SEGMENTS_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'DBMSG_DATABASE', p_sub_category_nlsid => 'DBMSG_SPACE_ISSUES', p_late_binding_target_types => l_target_types); -- ---------------------------------------------------------------- -- Element 3.1: Tablespaces with Problem Segments 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_TBSP_WITH_PROB_SEGS_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 => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 3.2: Tablespaces with Segments Approaching Max Extents -- -- Data Scope: Repository (Current) -- Chart Type: Table -- Agent Version: All -- -- Description: Shows current outstanding alerts for tablespaces -- with segments approaching max extents, including -- a count of violating segs and a list of segs. -- This is the only place where this data appears -- together. -- ------------------------------------------------------------ -- --------------------------------- -- 3.2 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ac.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ac.key_value AS TABLESPACE, ac.collection_timestamp as ALERT_TRIGGERED, substr(ac.message_params, 0, instr(ac.message_params, ''''&'''') - 1) as SEGMENT_COUNT, mc.value AS SEGMENTS FROM mgmt$alert_current ac, (SELECT target_guid, collection_timestamp, key_value, value FROM mgmt$metric_current WHERE target_guid=:1 AND metric_name=''''problemSegTbsp'''' AND metric_column=''''segsMaxExtsMessage'''') mc, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND ac.target_guid=t.target_guid AND ac.metric_name=''''problemSegTbsp'''' AND ac.metric_column=''''segsMaxExtsCnt'''' AND mc.target_guid=t.target_guid AND mc.key_value=ac.key_value ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,tgt_guid_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspSegsApproachingMaxExtents', p_sql_statement => l_sql); -- --------------------------------- -- 3.2 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); 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.TbspSegsApproachingMaxExtents'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TABLESPACE_SEGS_APPROACH_MAX_EXTENTS', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- Element 3.3: Tablespaces with Segments Not Able to Extend -- -- Data Scope: Repository (Current) -- Chart Type: Table -- Agent Version: All -- -- Description: Shows current outstanding alerts for tablespaces -- with segments not able to extend, including -- a count of violating segs and a list of segs. -- This is the only place where this data appears -- together. -- ------------------------------------------------------------ -- --------------------------------- -- 3.3 Register SQL for this element -- --------------------------------- l_sql := 'DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; query_string := '' SELECT decode(ac.alert_state, ''''Critical'''', 25, ''''Warning'''', 20, ''''Clear'''', 15) AS SEVERITY, ac.key_value AS TABLESPACE, ac.collection_timestamp as ALERT_TRIGGERED, substr(ac.message_params, 0, instr(ac.message_params, ''''&'''') - 1) as SEGMENT_COUNT, mc.value AS SEGMENTS FROM mgmt$alert_current ac, (SELECT target_guid, target_type, collection_timestamp, key_value, value FROM mgmt$metric_current WHERE target_guid=:1 AND metric_name=''''problemSegTbsp'''' AND metric_column=''''segsChunkSmlMessage'''') mc, (SELECT target_guid FROM mgmt$target WHERE target_guid=:1 AND (target_type=''''rac_database'''' OR (target_type=''''oracle_database'''' AND TYPE_QUALIFIER3 != ''''RACINST''''))) t WHERE ac.target_guid=:1 AND (ac.target_type=''''oracle_database'''' OR ac.target_type=''''rac_database'''') AND mc.target_guid = ac.target_guid AND (mc.target_type=''''oracle_database'''' OR mc.target_type=''''rac_database'''') AND ac.target_guid=t.target_guid AND ac.metric_name=''''problemSegTbsp'''' AND ac.metric_column=''''segsChunkSmlCnt'''' AND mc.target_guid=t.target_guid AND mc.key_value=ac.key_value ''; OPEN result_cursor_out for query_string using tgt_guid_in,tgt_guid_in,tgt_guid_in; END;'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspSegsNotAbleToExtend', p_sql_statement => l_sql); -- --------------------------------- -- 3.3 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); 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.TbspSegsNotAbleToExtend'); 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.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_USER_TABLE_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TABLESPACE_SEGS_NOT_ABLE_EXTEND', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null); -- ---------------------------------------------------- -- REPORT - Tablespace with Reclaimable Space -- ---------------------------------------------------- -- Element Definition Instruction text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); 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.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_INSTR_TEXT_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_INSTR_TEXT_REC_SPACE_DESC', 'oracle.sysman.db.util.reports.storage.SegAdvInstrTextRenderController', 1, null, l_param_values); -- Element Definition Last time the Segment Advisor task run 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.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stgAdv.StgAdvMsg'); 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'); mgmt_ip.add_element_definition ('DBMSG_LAST_RUN_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_LAST_RUN_REC_SPACE_DESC', 'oracle.sysman.db.util.reports.storage.RecSpaceLastRunRenderController', 1, null, l_param_values); -- Element Definition tablepace with reclaimable space from sql (sql is not editable by em user) 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.stgAdv.StgAdvMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', 'PROBCOL_RECLAIMABLE_SPACE, PROBCOL_WASTED_SPACE'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_TABLESPACE_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_TABLESPACE_REC_SPACE_DESC', 'oracle.sysman.db.util.reports.storage.TablespaceRecSpaceRenderController', 1, null, l_param_values); -- Element Definition segments with reclaimable space from sql (sql is not editable by em user) 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.stgAdv.StgAdvMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '4'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_SEGMENTS_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_SEGMENTS_REC_SPACE_DESC', 'oracle.sysman.db.util.reports.storage.SegmentsRecSpaceRenderController', 1, null, l_param_values); -- Report Definition l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(2); l_target_types(1) := 'oracle_database'; l_target_types(2) := 'rac_database'; l_report_guid := mgmt_ip.create_report_definition ( 'DBMSG_TABLESPACE_REC_SPACE', 'DBMSG_TABLESPACE_REC_SPACE_DESC', 'SYSMAN', 'DBMSG_DATABASE', 'DBMSG_SPACE_ISSUES', l_target_types, 0, 0, 1); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_INSTR_TEXT_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', '', 1, 1, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_LAST_RUN_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', '', 2, 2, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TABLESPACE_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', null, 3, 3, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SEGMENTS_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', null, 4, 4, null, null); -- ------------------------------------------------------------------------- -- REPORT - Segments with Row Chaining Issues -- ------------------------------------------------------------------------- -- Element Definition segments with row chaining issues from sql (sql is not editable by em user) 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.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stgAdv.StgAdvMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_SEGMENTS_ROW_CHAINING', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_SEGMENTS_ROW_CHAINING_DESC', 'oracle.sysman.db.util.reports.storage.SegmentsRowChainingRenderController', 1, null, l_param_values); -- Report Definition l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(2); l_target_types(1) := 'oracle_database'; l_target_types(2) := 'rac_database'; l_report_guid := mgmt_ip.create_report_definition ( 'DBMSG_SEGMENTS_ROW_CHAINING', 'DBMSG_SEGMENTS_ROW_CHAINING_DESC', 'SYSMAN', 'DBMSG_DATABASE', 'DBMSG_SPACE_ISSUES', l_target_types, 0, 0, 1); 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_TBSP_ROW_CHAIN_INSTR'); 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); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_LAST_RUN_REC_SPACE', 'IPMSG_DATABASE_TARGET_TYPE', '', 2, 2, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SEGMENTS_ROW_CHAINING', 'IPMSG_DATABASE_TARGET_TYPE', 'DBMSG_SEGMENTS_ROW_CHAINING', 3, 3, null, null); COMMIT; END; END; / SET DEFINE ON