Rem drv: Rem Rem $Header: db_outofbox_storage.sql 07-dec-2006.22:21:19 denath Exp $ Rem Rem db_outofbox_storage.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem db_outofbox_storage.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 12/05/06 - Bug Fix 5592216.Removed adjustTimes Report param Rem for 3.3 chart Monthly Cumulative Tablespace Space Rem Usage, BAR format. Rem denath 09/22/06 - Fix Bug 5455205.Changed query for Rem DBGroupCurrDBSpcUsage, Rem DBGroupHistStorageChartSql, Rem DBGroupTbspAllocUsage, Rem CurrDBGroupTbspFullAlertSummary, Rem DBGroupFullAlertCount. Rem qsong 09/07/05 - bug 4596524 Rem qsong 08/19/05 - 4396074 Rem qsong 07/25/05 - bug 4490041 Rem chyu 07/20/05 - modifying the type to post_creation Rem gsbhatia 07/11/05 - New repmgr header impl Rem rreilly 05/17/05 - bug 4373680 fix divide by 0 for tbsp size of 0 Rem rreilly 04/28/05 - bug 4262522 Rem qsong 04/13/05 - grabtrans 'rreilly_bug-4262522' Rem qsong 03/29/05 - Rem rreilly 04/05/05 - bug 4262522 make sure db elements do not run for Rem racinst. Rem xshen 03/21/05 - Add comments Rem qsong 02/23/05 - swap the instruction and separator position Rem pbantis 02/16/05 - Use db_init_params. Rem qsong 02/14/05 - Add instruction texts for the reports Rem mnihalan 01/13/05 - Change control file report Rem mnihalan 12/30/04 - Fix sql Rem mnihalan 12/21/04 - Fix bugs Rem qsong 12/13/04 - Rem mnihalan 12/09/04 - Change translated strings Rem qsong 11/12/04 - qsong_asm_reports Rem qsong 10/27/04 - Rem mnihalan 10/13/04 - Change layout of reports Rem mnihalan 10/04/04 - Add reports Rem mnihalan 09/30/04 - Created 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; -- ------------------------------------------------------------------------- -- REPORT 2: Database Space Usage -- -- Description: This report will contains a summary of database space usage. -- It shows space usage for the entire database, a break down -- of that usage by tablespace and datafile. -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 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:2'); l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_DATABASE_SPACE_USAGE', p_description_nlsid => 'DBMSG_DATABASE_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 2.1: Database Space Usage 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_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 2.2: Current Database Space Usage -- -- Data Scope: Repository (Latest Data) -- Chart Type: Label Value Pair -- Agent Version: -- 1) For 10.2 GC show all columns -- 2) 10.1.0.4 DB Control Agent show all columns -- 3) 10.1.0.4 Agent (no DB Control -- hide the used and free values (used size was added -- in 10.2 DB Control). -- NOTE: test the oracle_database.xml meta version for this -- -- Description: Shows 3 label/value pairs: -- Total Allocated Size (GB) -- Total Allocated Used (GB) -- Total Allocated Free (GB) -- ---------------------------------------------------------------- -- --------------------------------- -- 2.2 Register SQL for this element -- --------------------------------- l_sql := 'SELECT ROUND(SUM(t.tablespace_size/1024/1024/1024), 2) AS ALLOC_GB, ROUND(SUM(t.tablespace_used_size/1024/1024/1024), 2) AS USED_GB, ROUND(SUM((t.tablespace_size - tablespace_used_size)/1024/1024/1024), 2) AS ALLOC_FREE_GB FROM mgmt$db_tablespaces t, (SELECT target_guid FROM mgmt$target WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND (target_type=''rac_database'' OR (target_type=''oracle_database'' AND TYPE_QUALIFIER3 != ''RACINST''))) tg WHERE t.target_guid=tg.target_guid '; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.CurrentDBStorageSql', 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(4); 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.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.CurrentDBStorageSql'); 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_CURRENT_DATABASE_SPACE_USAGE_DESC', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 2.3: Historical Tablespace Space Usage -- -- Data Scope: Repository (Metric Data) -- Chart Type: Stacked Line Chart -- Agent Version: 10.2 Grid Control Agent -- (historical metric was added in 10.2 GC) -- For pre-10.2 GC agents, hide this chart -- -- Description: Shows all available history for 2 values: -- Total Allocated Size (GB) <-- Free (GB) line -- Total Allocated Used (GB) <-- Used (GB) line -- -- ------------------------------------------------------------ -- --------------------------------- -- 2.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, sum(m.average/1024) AS value FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_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.DBHistTbspUsageChartSql', 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.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.DBHistTbspUsageChartSql'); 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.emo.util.reports.ChartVersionRender.agentVersionGtOrEq', '10.2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'SIZE_GB'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_param_values(9) := 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_CHART_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_HISTORICAL_DATABASE_SPACE_USAGE', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 2.4: Tablespace Space Usage -- -- Data Scope: Repository (ECM Metric Data) -- Chart Type: Table -- Agent Version: 10.2 Grid Control Agent has the tbspAllocation -- metric that is needed for this chart. -- However, this is supposed to be backported to -- The 10.1.0.5 Agent. -- Description: Shows all current tbsp info, size, config info -- for all tablespaces in the db. -- ------------------------------------------------------------ -- --------------------------------- -- 2.4 Register SQL for this element -- --------------------------------- l_sql := 'SELECT ts.tablespace_name AS TBNAME, round((ts.tablespace_size/1024/1024),2) AS SIZE_MB, round((ts.tablespace_used_size/1024/1024),2) AS USED_MB, round(((ts.tablespace_size - ts.tablespace_used_size)/1024/1024),2) AS FREE_MB, round(((ts.tablespace_used_size * 100)/ decode(ts.tablespace_size,0,1,ts.tablespace_size)) ,2) AS USED_PCT, decode(df.auto_extend, 0, ''NO'' , ''YES'') AS AUTO_EXTEND, ts.status AS STATUS, round(df.df_count,0) AS DATAFILES, ts.contents AS TYPE, ts.extent_management AS EXTENT_MANAGEMENT, ts.segment_space_management AS SEGMENT_MANAGEMENT FROM mgmt$db_tablespaces ts, (SELECT d.target_guid, d.tablespace_name, count(d.file_name) df_count, sum(decode(d.autoextensible, ''YES'', 1, 0)) auto_extend FROM mgmt$db_datafiles d, mgmt$target t WHERE t.target_guid = ??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND t.target_guid = d.target_guid GROUP BY d.target_guid, d.tablespace_name) df WHERE ts.target_guid = df.target_guid AND df.tablespace_name = ts.tablespace_name ORDER BY ts.tablespace_size'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBTablespaceSpaceUsageTable', 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.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stor.TablespaceMsg'); 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.initialSortColumn', '1'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'CURRENT_ALLOCATED'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '1'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '4'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBTablespaceSpaceUsageTable'); 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_TBLSPC_ALLOCATION_SPACE_USAGE_DESC', p_element_order => 4, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 2.5: Datafile Space Usage -- -- Data Scope: Repository (ECM Metric Data) -- Chart Type: Table -- Agent Version: All -- Description: Shows all current datafile info, size, config info -- for all datafiles in the db. -- ------------------------------------------------------------ -- --------------------------------- -- 2.5 Register SQL for this element -- --------------------------------- l_sql := 'SELECT d.file_name AS FILE_NAME, ROUND(d.file_size/1024/1024, 2) AS FILE_SIZE_MB, d.tablespace_name AS TABLESPACE, d.status AS STATUS, d.autoextensible AS AUTO_EXTEND, ROUND(d.max_file_size/1024/1024, 2) AS MAX_FILE_SIZE_MB, ROUND(d.increment_by, 2) AS INCREMENT_SIZE_MB FROM mgmt$db_datafiles d, mgmt$target t WHERE t.target_guid = ??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND t.target_guid = d.target_guid ORDER BY d.file_size DESC'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBDatafileSpcUsage', p_sql_statement => l_sql); -- --------------------------------- -- 2.5 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.stor.DatafileMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.DBDatafileSpcUsage'); 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_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_DATAFILE_SPACE_USAGE', p_element_order => 5, p_element_row => 4, p_parameters => l_param_values, p_targets => null); COMMIT; -- ------------------------------------------------------------------------- -- REPORT 3: Monthly Tablespace Space Usage Details -- -- Description: This report will contain a summary of tablespace space usage. -- It contains a monthly summary rollup, a graph, and then a -- monthly breakdown of each tablespace. -- DEFAULT TimePeriod: Last 31 Days -- ------------------------------------------------------------------------- -- ------------------------------------------- -- 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'; -- 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 -- 2:1 This month 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'); -- We change the default time period to last 31 days because the metric colleciton frequency -- is 7 days, setting the default time to "This month" might cause the chart to show no data -- depending on what date the user is looking at the chart( eg. Sept. 2nd) 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_TBLSPC_MONTHLY_SPACE_USAGE', p_description_nlsid => 'DBMSG_TBLSPC_MONTHLY_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 3.1: Monthly Tablespace Space Usage 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', 'TBSP_MONTHLY_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); -- ------------------------------------------------------------ -- CHART 3.2: Tablespace Monthly Space Usage Report -- Collection Timestamp Text -- -- -- Data Scope: Repository (Historical) -- Chart Type: Label/Value Pair -- Agent Version: 10.2 - uses the new tbspAllocation metric, may -- be backported to 10.1.0.5 agent -- Description: Shows the earliest and latest collection timestamp -- ------------------------------------------------------------ -- --------------------------------- -- 3.2 Register SQL for this element -- --------------------------------- l_sql := 'SELECT min(d.rollup_timestamp) as EARLEST_COLLECTION_TIMESTAMP, max(d.rollup_timestamp) as LATEST_COLLECTION_TIMESTAMP FROM mgmt$metric_daily d, mgmt$target t WHERE d.target_guid = ??EMIP_BIND_TARGET_GUID?? AND (d.target_type = ''rac_database'' OR d.target_type=''oracle_database'') AND d.metric_name = ''tbspAllocation'' AND (d.metric_column = ''spaceAllocated'' OR d.metric_column = ''spaceUsed'') AND d.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND d.rollup_timestamp <= ??EMIP_BIND_END_DATE?? AND t.target_guid = d.target_guid AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) '; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspAllocMetricTimestampSql', 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(5); 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.TbspAllocMetricTimestampSql'); l_param_values(5) := 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 => '', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 3.3: Monthly Cumulative Tablespace Space Usage -- [BAR CHART] -- -- Data Scope: Repository (Historical) -- Chart Type: Bar Chart -- Agent Version: 10.2 - uses the new tbspAllocation metric, may -- be backported to 10.1.0.5 agent -- Description: Shows db size on a per month basis. -- ------------------------------------------------------------ -- --------------------------------- -- 3.3 Register SQL for this element -- --------------------------------- l_sql := 'SELECT decode(metric_column, ''spaceAllocated'', ''AVERAGE_SIZE_GB'', ''spaceUsed'', ''AVERAGE_USED_GB''), month_timestamp, avg(value) FROM (SELECT m.metric_column AS metric_column, TO_DATE(TO_CHAR(m.rollup_timestamp,''YYYY-MM''), ''YYYY-MM'') AS month_timestamp, sum(m.average/1024) AS value FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_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) GROUP BY metric_column, month_timestamp ORDER BY month_timestamp ASC'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlyChartSql', 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.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.TbspUsageMonthlyChartSql'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'timeSeriesBarChart'); 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_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'DBMSG_USER_CHART_FROM_SQL_ELEM', p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE', p_header_nlsid => 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 3.4: Monthly Cumulative Tablespace Space Usage -- [TABLE] -- -- Data Scope: Repository (Historical) -- Chart Type: Table Pair -- Agent Version: 10.2 - uses the new tbspAllocation metric, may -- be backported to 10.1.0.5 agent -- Description: Table of montly rollup of Average, Max and Min sizes -- of all the tablespaces in the database -- -- How to get these calculations: -- 1) To start we need to sum the size of all the tablespace -- on a certain day to give us the db size on that day -- 2) Then to get the average sizes, calculate for each day and -- then take the average -- 3) For the minimum it's the same, calculate day first, then -- take the minimum -- 4) For the maximum it's the same, calculate day first, then -- take the maximum -- ------------------------------------------------------------ -- --------------------------------- -- 3.4 Register SQL for this element -- --------------------------------- l_sql := 'SELECT TO_DATE(TO_CHAR(a_size.month_timestamp,''MON RR''),''MON RR'') AS CALENDAR_MONTH, round(avg(a_size.size_gb),2) AS SIZE_GB, round(avg(used.used_gb),2) AS USED_GB, round(avg(a_size.size_gb - used.used_gb),2) AS FREE_GB, round(avg((used.used_gb*100)/ decode(a_size.size_gb,0,1,a_size.size_gb)) ,2) AS USED_PCT, round(max(a_size.size_gb),2) AS SIZE_GB, round(max(used.used_gb),2) AS USED_GB, round(max(a_size.size_gb - used.used_gb),2) AS FREE_GB, round(max((used.used_gb*100)/ decode(a_size.size_gb,0,1,a_size.size_gb)) ,2) AS USED_PCT, round(min(a_size.size_gb),2) AS SIZE_GB, round(min(used.used_gb),2) AS USED_GB, round(min(a_size.size_gb - used.used_gb),2) AS FREE_GB, round(min((used.used_gb*100)/ decode(a_size.size_gb,0,1,a_size.size_gb)) ,2) AS USED_PCT FROM (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS size_gb FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_name=''tbspAllocation'' AND (m.metric_column=''spaceAllocated'') AND m.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND m.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY m.metric_column, m.rollup_timestamp) a_size, (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS used_gb FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_name=''tbspAllocation'' AND (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) used WHERE a_size.month_timestamp =used.month_timestamp GROUP BY TO_CHAR(a_size.month_timestamp,''MON RR'')'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlyCumulativeTable', p_sql_statement => l_sql); -- --------------------------------- -- 3.4 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(14); 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.NamedSqlStatement', 'oracle.sysman.db.storage.reports.TbspUsageMonthlyCumulativeTable'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'AVERAGE_ALLOCATED'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '4'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'MAXIMUM_ALLOCATED'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '5'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '8'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader3', 'MINIMUM_ALLOCATED'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol3', '9'); l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol3', '12'); l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(14) := 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 => '', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null); -- ------------------------------------------------------------ -- CHART 3.5: Monthly Tablespace Space Usage Details -- [SPLIT TABLE : Split by Month into several tables] -- -- Data Scope: Repository (Historical) -- Chart Type: Split Table -- Agent Version: 10.2 - uses the new tbspAllocation metric, may -- be backported to 10.1.0.5 agent -- Description: Table of montly rollup of Average, Max and Min sizes -- of all the tablespaces in the database -- -- How to get these calculations: -- -- This is the individual tablespaces, NOT the whole db -- so min is the min of the tablespace for the month, -- max is the max of the tablespace for the month. -- This calculation is not as complex as the total db size calculations. -- However, when calculating things like Min Free (MB) we still need to -- deal with the data on a day by day basis, IE, don't take min Size from -- one day and the min Used from another day... -- 1) To start we need the avg size of the tablespace for the month -- and the avg used space of the tablespace for the month. -- 2) Then to get the average sizes, sum for each day of the month -- and then take the average -- 3) For the minimum, calculate the size for the day first, then -- take the minimum for all the days -- 4) For the maximum it's the same, calculate the size for the -- day first, then take the maximum -- ------------------------------------------------------------ -- --------------------------------- -- 3.5 Register SQL for this element -- --------------------------------- l_sql := 'SELECT TO_DATE(TO_CHAR(alloc.timestamp,''MON RR''),''MON RR'') AS CALENDAR_MONTH, alloc.tablespace AS TABLESPACE, round(avg(alloc.avg_size_mb),2) AS SIZE_MB, round(avg(used.avg_used_mb),2) AS USED_MB, round(avg(alloc.avg_size_mb - used.avg_used_mb),2) AS FREE_MB, round(avg((used.avg_used_mb*100)/ decode(alloc.avg_size_mb,0,1,alloc.avg_size_mb)) ,2) AS USED_PCT, round(max(alloc.max_size_mb),2) AS SIZE_MB, round(max(used.max_used_mb),2) AS USED_MB, round(max(alloc.avg_size_mb - used.avg_used_mb),2) AS FREE_MB, round(max((used.avg_used_mb*100)/ decode(alloc.avg_size_mb,0,1,alloc.avg_size_mb)) ,2) AS USED_PCT, round(min(alloc.min_size_mb),2) AS SIZE_MB, round(min(used.min_used_mb),2) AS USED_MB, round(min(alloc.avg_size_mb - used.avg_used_mb),2) AS FREE_MB, round(min((used.avg_used_mb*100)/ decode(alloc.avg_size_mb,0,1,alloc.avg_size_mb)) ,2) AS USED_PCT FROM (SELECT m.key_value as tablespace, m.rollup_timestamp AS timestamp, avg(m.average) AS avg_size_mb, min(m.minimum) AS min_size_mb, max(m.maximum) AS max_size_mb FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_name=''tbspAllocation'' AND (m.metric_column=''spaceAllocated'') AND m.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND m.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY m.rollup_timestamp, m.key_value) alloc, (SELECT m.key_value as tablespace, m.rollup_timestamp AS timestamp, avg(m.average) AS avg_used_mb, min(m.minimum) AS min_used_mb, max(m.maximum) AS max_used_mb FROM mgmt$metric_daily m, mgmt$target t WHERE t.target_guid=??EMIP_BIND_TARGET_GUID?? AND (t.target_type=''rac_database'' OR (t.target_type=''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) AND m.target_guid=t.target_guid AND m.metric_name=''tbspAllocation'' AND (m.metric_column=''spaceUsed'') AND m.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND m.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY m.rollup_timestamp, m.key_value) used WHERE alloc.timestamp=used.timestamp AND alloc.tablespace=used.tablespace GROUP BY TO_CHAR(alloc.timestamp,''MON RR''), alloc.tablespace'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlyDetailsSplitTable', p_sql_statement => l_sql); -- --------------------------------- -- 3.5 Add this Element To the Report -- --------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(16); 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.NamedSqlStatement', 'oracle.sysman.db.storage.reports.TbspUsageMonthlyDetailsSplitTable'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'AVERAGE_ALLOCATED'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '5'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'MAXIMUM_ALLOCATED'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '6'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '9'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader3', 'MINIMUM_ALLOCATED'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol3', '10'); l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol3', '13'); l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(14) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(15) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', 'CALENDAR_MONTH'); l_param_values(16) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.tableHeaderMsgID', 'MONTHLY_TBSP_USAGE_BY_MONTH_HEADER'); 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_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null); COMMIT; END; / SET DEFINE ON