Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\reports\db_outofbox_storage.sql
Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="reports/db_outofbox_audit.sql+" condition="EM_REPOS_MODE=CENTRAL"/> Rem Rem $Header: db_outofbox_storage.sql 28-nov-2006.06:34:03 mnihalan 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 - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem !!! Please update upgrade script if updates are made. !!! Rem Rem MODIFIED (MM/DD/YY) Rem mnihalan 11/28/06 - Fix bug 5683385 Rem qsong 08/25/05 - 4574597 Rem qsong 08/22/05 - 4396074 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 BEGIN 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 -- ------------------------------------------------------------------------- -- Element and Report Definitions -- ------------------------------------------------------------------------- -- ------------------------------------------------------------------------- -- Text Field Instructions -- ------------------------------------------------------------------------- -- Database Group Space Usage Report Instructions 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'); mgmt_ip.add_element_definition ('DBMSG_GROUP_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Database Group Space Usage Report Instructions 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'); mgmt_ip.add_element_definition ('DBMSG_GROUP_SPACE_USAGE_TBSPFULL_ALERT_INSTRUCTION', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Database Space Usage Report Instructions 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'); mgmt_ip.add_element_definition ('DBMSG_DB_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Tablespace Monthly Space Usage Report Instructions 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'); mgmt_ip.add_element_definition ('DBMSG_TBSP_MONTHLY_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Tablespace Monthly Space Usage Report Collection Timestamp Text l_sql := 'SELECT min(rollup_timestamp) as EARLEST_COLLECTION_TIMESTAMP, max(rollup_timestamp) as LATEST_COLLECTION_TIMESTAMP FROM mgmt$metric_daily WHERE metric_name = ''tbspAllocation'' AND metric_column = ''spaceAllocated'' OR metric_column = ''spaceUsed'''; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspAllocMetricTimestampSql', p_sql_statement => l_sql); 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.TbspAllocMetricTimestampSql'); mgmt_ip.add_element_definition ('DBMSG_TBSP_USAGE_COLLECTION_TIMESTAMP', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- --------------------------------------- -- REPORT - Database Space Usage Summary report -- --------------------------------------- -- ------------------------------------------------------------ -- NAME VALUE 1.1: Current Tablespace 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) -- -- ------------------------------------------------------------ -- Element Definition Historical Database Total Space Usage - chart -- ------------------------------------------------------------ -- CHART 1.2: 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 -- -- ------------------------------------------------------------ l_sql := 'SELECT ''TOTAL_ALLOCATED_GB'', ''USED_GB'', f.rollup_timestamp AS timestamp, round(f.total_gb,2) AS allocated_free_gb, round(u.used_gb,2) AS allocated_used_gb FROM ( SELECT rollup_timestamp, sum(average/1024) AS total_gb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceAllocated'' GROUP BY rollup_timestamp ) f, ( SELECT rollup_timestamp, sum(average/1024) AS used_gb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceUsed'' GROUP BY rollup_timestamp ) u WHERE f.rollup_timestamp = u.rollup_timestamp AND f.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND f.rollup_timestamp <= ??EMIP_BIND_END_DATE?? order by timestamp'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBHistTbspUsageChartSql', p_sql_statement => l_sql); 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.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'); mgmt_ip.add_element_definition ('DBMSG_HISTORICAL_DATABASE_SPACE_USAGE', 'database_target_type', 'DBMSG_HISTORICAL_DATABASE_SPACE_USAGE_DESC', 'oracle.sysman.db.util.reports.storage.StorageChartRenderController', 1, null, l_param_values); -- Element Definition - Current Tablespace Allocation and Space Usage - table -- ------------------------------------------------------------ -- CHART 1.3: Tablespace Allocation and Space Usage -- -- Data Scope: Repository (Latest and Historical Data) -- Chart Type: Table -- Agent Version: -- 1) For 10.2 GC Agent show all columns -- 2) 10.1.0.4 DB Control Agent -- * Since tbspAllocation is only available in 10.2GC -- hide the 2 Avg and 2 Max columns, they're historical -- so they require the metric. -- * Hide the Segment Space Management column -- (it was added in 10.2GC) -- 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 tablespace in -- the database. Historical columns will be -- based on all historical data available in the -- database. The table will contain the following -- columns: -- Tablespace Name -- Current Allocated Size (MB) -- Current Allocated Used (MB) -- Current Allocated Free (MB) -- Current Allocated Used (%) -- Avg Allocated Size (MB) -- Avg Allocated Used (MB) -- Max Allocated Size (MB) -- Max Allocated Used (MB) -- Autoextensible -- Status -- # Datafiles -- Type -- Extent Management -- Segment Space Management <-- data not currently available -- ------------------------------------------------------------ l_sql := 'SELECT u.timestamp TIME_STAMP, ts.tablespace_name AS TBNAME, round(t.avg_size_mb,2) AS SIZE_MB, round(u.avg_used_mb,2) AS USED_MB, round((t.avg_size_mb - u.avg_used_mb),2) AS FREE_MB, round(u.avg_used_mb*100/t.avg_size_mb,2) AS USED_PCT, round(t.max_size_mb,2) AS SIZE_MB, round(u.max_used_mb,2) AS USED_MB, round((t.max_size_mb - u.max_used_mb),2) AS FREE_MB, round((u.max_used_mb*100/t.max_size_mb),2) AS USED_PCT, round(t.min_size_mb,2) AS SIZE_MB, round(u.min_used_mb,2) AS USED_MB, round((t.min_size_mb - u.min_used_mb),2) AS FREE_MB, round((u.min_used_mb*100/t.min_size_mb),2) AS USED_PCT FROM mgmt$db_tablespaces ts, (SELECT key_value AS tbsp_name, avg(average) AS avg_size_mb, max(maximum) AS max_size_mb, min(minimum) AS min_size_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceAllocated'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY key_value) t, (SELECT TO_CHAR(rollup_timestamp, ''MON RR'') as timestamp, key_value AS tbsp_name, avg(average) AS avg_used_mb, max(maximum) AS max_used_mb, min(minimum) AS min_used_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceUsed'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY TO_CHAR(rollup_timestamp, ''MON RR''), key_value) u, (SELECT tablespace_name, count(file_name) df_count, sum(decode(autoextensible, ''YES'', 1, 0)) auto_extend FROM mgmt$db_datafiles WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? GROUP BY tablespace_name) df WHERE t.tbsp_name(+) = u.tbsp_name AND ts.target_guid = ??EMIP_BIND_TARGET_GUID?? AND ts.tablespace_name = u.tbsp_name(+) AND df.tablespace_name = ts.tablespace_name'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthly', p_sql_statement => l_sql); 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.tableSplitColumn', 'TIME_STAMP'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.tableHeaderMsgID', 'MONTHLY_TBSP_USAGE_BY_MONTH_HEADER'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stor.TablespaceMsg'); 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.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', 'AVERAGE_HDR'); l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol1', '2'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol1', '5'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'MAXIMUM_HDR'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '6'); l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '9'); l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader3', 'MINIMUM_HDR'); l_param_values(14) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol3', '10'); l_param_values(15) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol3', '13'); l_param_values(16) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.TbspUsageMonthly'); mgmt_ip.add_element_definition ('DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- CHART 1.3: Monthly Tablespace Allocation and Space Usage Summary -- -- Data Scope: Repository (Latest and Historical Data) -- Chart Type: Table -- Agent Version: -- 1) For 10.2 GC Agent show all columns -- 2) 10.1.0.4 DB Control Agent -- * Since tbspAllocation is only available in 10.2GC -- hide the 2 Avg and 2 Max columns, they're historical -- so they require the metric. -- * Hide the Segment Space Management column -- (it was added in 10.2GC) -- 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 tablespace in -- the database. Historical columns will be -- based on all historical data available in the -- database. The table will contain the following -- columns: -- Collection timestamp -- Current Allocated Size (MB) -- Current Allocated Used (MB) -- Current Allocated Free (MB) -- Current Allocated Used (%) -- Avg Allocated Size (MB) -- Avg Allocated Used (MB) -- Max Allocated Size (MB) -- Max Allocated Used (MB) -- ------------------------------------------------------------ l_sql := 'SELECT u.timestamp CALENDAR_MONTH, sum(round(t.avg_size_mb,2)) AS SIZE_MB, sum(round(u.avg_used_mb,2)) AS USED_MB, round(sum(t.avg_size_mb) - sum(u.avg_used_mb), 2) AS FREE_MB, round((sum(u.avg_used_mb) * 100)/sum(t.avg_size_mb), 2) AS USED_PCT, sum(round(t.max_size_mb,2)) AS SIZE_MB, sum(round(u.max_used_mb,2)) AS USED_MB, round(sum(t.max_size_mb) - sum(u.max_used_mb), 2) AS FREE_MB, round((sum(u.max_used_mb) * 100)/sum(t.max_size_mb), 2) AS USED_PCT, sum(round(t.min_size_mb,2)) AS SIZE_MB, sum(round(u.min_used_mb,2)) AS USED_MB, round(sum(t.min_size_mb) - sum(u.min_used_mb), 2) AS FREE_MB, round((sum(u.min_used_mb) * 100)/sum(t.min_size_mb), 2) AS USED_PCT FROM mgmt$db_tablespaces ts, (SELECT key_value AS tbsp_name, avg(average) AS avg_size_mb, max(maximum) AS max_size_mb, min(minimum) AS min_size_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceAllocated'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY key_value) t, (SELECT TO_DATE(TO_CHAR(rollup_timestamp, ''MON RR''),''MON RR'') as timestamp, key_value AS tbsp_name, avg(average) AS avg_used_mb, max(maximum) AS max_used_mb, min(minimum) AS min_used_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceUsed'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY TO_DATE(TO_CHAR(rollup_timestamp, ''MON RR''), ''MON RR''), key_value) u, (SELECT tablespace_name, count(file_name) df_count, sum(decode(autoextensible, ''YES'', 1, 0)) auto_extend FROM mgmt$db_datafiles WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? GROUP BY tablespace_name) df WHERE t.tbsp_name(+) = u.tbsp_name AND ts.target_guid = ??EMIP_BIND_TARGET_GUID?? AND ts.tablespace_name = u.tbsp_name(+) AND df.tablespace_name = ts.tablespace_name group by timestamp order by timestamp desc'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlySplit', p_sql_statement => l_sql); 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.tableHeaderMsgID', 'MONTHLY_TBSP_USAGE_BY_MONTH_HEADER'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.stor.TablespaceMsg'); 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', 'false'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.storage.reports.TbspUsageMonthlySplit'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader1', 'AVERAGE_HDR'); 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', '4'); l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader2', 'MAXIMUM_HDR'); l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol2', '5'); l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol2', '8'); l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader3', 'MINIMUM_HDR'); l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol3', '9'); l_param_values(14) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol3', '12'); l_param_values(15) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(16) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY_DESC', 'oracle.sysman.db.util.reports.storage.MonthlyTbspAllocSpaceSummaryRenderController', 1, null, l_param_values); -- Element Definition - Historical Tablespace Allocation and Usage Monthly Chart l_sql := 'SELECT TO_DATE(u.timestamp,''YYYY-MM'') TIME_STAMP, sum(round(t.avg_size_mb, 2)) AS AVERAGE_SIZE, sum(round(u.avg_used_mb,2)) AS AVERAGE_USED FROM mgmt$db_tablespaces ts, (SELECT key_value AS tbsp_name, avg(average) AS avg_size_mb, max(maximum) AS max_size_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceAllocated'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY key_value) t, (SELECT TO_CHAR(rollup_timestamp, ''YYYY-MM'') as timestamp, key_value AS tbsp_name, avg(average) AS avg_used_mb, max(maximum) AS max_used_mb FROM mgmt$metric_daily WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? AND metric_name=''tbspAllocation'' AND metric_column=''spaceUsed'' AND rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY TO_CHAR(rollup_timestamp, ''YYYY-MM''), key_value) u, (SELECT tablespace_name, count(file_name) df_count, sum(decode(autoextensible, ''YES'', 1, 0)) auto_extend FROM mgmt$db_datafiles WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? GROUP BY tablespace_name) df WHERE t.tbsp_name(+) = u.tbsp_name AND ts.target_guid = ??EMIP_BIND_TARGET_GUID?? AND ts.tablespace_name = u.tbsp_name(+) AND df.tablespace_name = ts.tablespace_name group by timestamp order by timestamp'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlyChartSql', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.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.TbspUsageMonthlyChartSql'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'MB'); mgmt_ip.add_element_definition ('DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY_CHART', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY_CHART_DESC', 'oracle.sysman.db.util.reports.storage.MonthlyTbspAllocSpaceRenderController', 1, null, l_param_values); -- Element Definition Recovery Area size l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); 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', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in MGMT_IP_TGT_GUID_ARRAY; start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ?; tgt_guid_in := ?; start_date_in := ?; end_date_in := ?; query_string := ''SELECT nvl(ra_loc.value, '''' '''') AS RA_LOCATION, ra_size.value AS RA_SIZE FROM (SELECT p.value FROM mgmt_db_init_params_ecm p, mgmt_ecm_gen_snapshot s WHERE p.ecm_snapshot_id = s.snapshot_guid AND s.target_guid = :1 AND (s.snapshot_type=''''oracle_dbconfig'''' OR s.snapshot_type=''''oracle_racconfig'''') AND s.is_current=''''Y'''' AND p.name=''''db_recovery_file_dest'''') ra_loc, (SELECT p.value FROM mgmt_db_init_params_ecm p, mgmt_ecm_gen_snapshot s WHERE p.ecm_snapshot_id = s.snapshot_guid AND s.target_guid = :1 AND (s.snapshot_type=''''oracle_dbconfig'''' OR s.snapshot_type=''''oracle_racconfig'''') AND s.is_current=''''Y'''' AND p.name=''''db_recovery_file_dest_size'''') ra_size ''; OPEN result_cursor_out for query_string using tgt_guid_in(1), tgt_guid_in(1); END; END;'); 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.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.emo.util.reports.TableDBVersionRender.dbVersionGtOrEq', '10'); mgmt_ip.add_element_definition ('DBMSG_RECOVERY_AREA_SIZE', 'database_target_type', 'DBMSG_RECOVERY_AREA_SIZE_DESC', 'oracle.sysman.db.util.reports.TableDBVersionRenderController', 1, null, l_param_values); -- Element Definition Datafile Space Usage 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 WHERE target_guid = ??EMIP_BIND_TARGET_GUID?? ORDER BY HOST_NAME, FILE_SIZE_MB DESC'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBDatafileSpcUsage', p_sql_statement => l_sql); 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'); mgmt_ip.add_element_definition ('DBMSG_DATAFILE_SPACE_USAGE', 'database_target_type', 'DBMSG_DATAFILE_SPACE_USAGE_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- Report Definition l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; 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'); -- space usage for last 7 days 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_sub_category_nlsid => 'DBMSG_SPACE_USAGE', p_category_nlsid => 'DBMSG_DATABASE', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_DB_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); -- Element Definition - Total Tablespace Allocation and Space Usage - name value pair 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_ecm t, mgmt_ecm_gen_snapshot s WHERE t.ecm_snapshot_id = s.snapshot_guid AND s.target_guid=??EMIP_BIND_TARGET_GUID?? AND (s.snapshot_type=''oracle_dbconfig'' OR s.snapshot_type=''oracle_racconfig'') AND s.is_current=''Y'''; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.CurrentDBStorageSql', p_sql_statement => l_sql); 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'); -- Element 1 - Current Database Space Usage Area 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_CURRENT_DATABASE_SPACE_USAGE_DESC', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null); -- Element 2 - Historical Database Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_HISTORICAL_DATABASE_SPACE_USAGE', 'database_target_type', 'DBMSG_HISTORICAL_DATABASE_SPACE_USAGE', 3, 2, null, null); -- - Element 3 Tablespace Allocation and Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_DESC', 4, 3, null, null); -- - Element 4 Datafile Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_DATAFILE_SPACE_USAGE', 'database_target_type', 'DBMSG_DATAFILE_SPACE_USAGE', 5, 4, null, null); -- ------------------------------------------------------------------------- -- REPORT - Tablespace Space Usage Summary -- ------------------------------------------------------------------------- -- Element Definition 1 - Total Tablespace Allocation and Space Usage - nameValue pair 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.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'); mgmt_ip.add_element_definition ('DBMSG_TOTAL_TBLSPC_ALLOCATION_SPACE_USAGE', 'database_target_type', 'DBMSG_TOTAL_TBLSPC_ALLOCATION_SPACE_USAGE_DESC', 'oracle.sysman.db.util.reports.storage.CurrentTablespaceSpaceRenderController', 1, null, l_param_values); -- Element Definition 2 - Current Tablespace Allocation and Space Usage - table 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.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'); mgmt_ip.add_element_definition ('DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_DESC', 'oracle.sysman.db.util.reports.storage.TablespaceAllocSpaceRenderController', 1, null, l_param_values); -- Report Definition l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; 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', '1:3:2'); -- monthly report for the last 3 months 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_sub_category_nlsid => 'DBMSG_SPACE_USAGE', p_category_nlsid => 'DBMSG_DATABASE', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBSP_MONTHLY_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); -- - Element 2 Tablespace Allocation and Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBSP_USAGE_COLLECTION_TIMESTAMP', 'database_target_type', '', 2, 2, null, null); -- - Element 2 Tablespace Allocation and Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY_DESC', 3, 3, null, null); -- - Element 2 Tablespace Allocation and Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_SUMMARY_CHART', 'database_target_type', '', 4, 4, null, null); -- - Element 2 Tablespace Allocation and Space Usage l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY', 'database_target_type', 'DBMSG_TBLSPC_ALLOCATION_SPACE_USAGE_MONTHLY_DESC', 5, 5, null, null); ----------------------------------------------------------- -- Database Group Report ----------------------------------------------------------- -- Element Definition - Total Tablespace Allocation and Space Usage - name value pair -- ------------------------------------------------------------ -- CHART 2.1: Current Group Tablespace 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) -- -- ------------------------------------------------------------ l_sql := 'SELECT round(sum(t.tablespace_size/1024/1024/1024),2) AS TOTAL_SIZE_GB, round(sum(t.tablespace_used_size/1024/1024/1024),2) AS TOTAL_USED_GB, round(sum((t.tablespace_size-t.tablespace_used_size)/1024/1024/1024),2) AS TOTAL_FREE_GB FROM mgmt$db_tablespaces t, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE t.target_guid=dbs.target_guid'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupCurrDBSpcUsage', p_sql_statement => l_sql); 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.DBGroupCurrDBSpcUsage'); mgmt_ip.add_element_definition ('DBMSG_CURRENT_DBGROUP_CUMULATIVE_SPACE_USAGE', 'database_target_type', 'DBMSG_CURRENT_DBGROUP_CUMULATIVE_SPACE_USAGE_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- CHART 2.2: Historical Group 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 -- -- ------------------------------------------------------------ -- Element Definition Historical Database Total Space Usage - Chart l_sql := 'SELECT ''TOTAL_ALLOCATED_GB'', ''USED_GB'', a.rollup_timestamp AS rollup_timestamp, round(a.allocated_gb,2) AS total_allocated_gb, round(u.used_gb,2) AS total_used_gb FROM ( SELECT m.rollup_timestamp AS rollup_timestamp, sum(m.average/1024) AS allocated_gb FROM mgmt$metric_daily m, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE m.target_guid=dbs.target_guid AND m.metric_name=''tbspAllocation'' AND m.metric_column=''spaceAllocated'' GROUP BY m.rollup_timestamp ) a, ( SELECT m.rollup_timestamp AS rollup_timestamp, sum(m.average/1024) AS used_gb FROM mgmt$metric_daily m, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE m.target_guid=dbs.target_guid AND m.metric_name=''tbspAllocation'' AND m.metric_column=''spaceUsed'' GROUP BY m.rollup_timestamp ) u WHERE a.rollup_timestamp=u.rollup_timestamp AND a.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND a.rollup_timestamp <= ??EMIP_BIND_END_DATE??'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupHistStorageChartSql', p_sql_statement => l_sql); 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.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'); mgmt_ip.add_element_definition ('DBMSG_HISTORICAL_GROUP_CUMULATIVE_SPACE_USAGE', 'database_target_type', 'DBMSG_HISTORICAL_GROUP_CUMULATIVE_SPACE_USAGE_DESC', 'oracle.sysman.db.util.reports.storage.StorageChartRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- CHART 2.3: Database Group Open Alert Count for Tablespace Full Alert -- -- Data Scope: Repository (Latest) -- Chart Type: Label/Value Pairs -- Agent Version: -- 1) For 10.2 GC Agent and 10.1.0.4 DB Control Agent -- show all columns -- 2) For Pre-10.1.0.4 DB Control Agent -- hide the 2 Free Space(MB) columns -- NOTE: test the oracle_database.xml meta version for this -- -- Description: This table will report on every databsae in -- the group. It will give warning and critical -- alert counts: -- Critical Space Used(%) Count -- Warning Space Used(%) Count -- Critical Free Space(MB) Count -- Critical Free Space(MB) Count -- -- Queries: VIEW and BASE TABLE -- ------------------------------------------------------------ l_sql := 'SELECT round(su_c.num_su_alerts_crit,0) AS SPC_USED_CRIT_ALERT_CNT, round(su_w.num_su_alerts_warn,0) AS SPC_USED_WRNG_ALERT_CNT, round(bf_c.num_bf_alerts_crit,0) AS FREE_SPC_CRIT_ALERT_CNT, round(bf_w.num_bf_alerts_warn,0) AS FREE_SPC_WRNG_ALERT_CNT FROM ( SELECT count(ac.alert_state) AS num_su_alerts_crit FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=25) su_c, ( SELECT count(ac.alert_state) AS num_su_alerts_warn FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=20) su_w, ( SELECT count(ac.alert_state) AS num_bf_alerts_crit FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=25) bf_c, ( SELECT count(ac.alert_state) AS num_bf_alerts_warn FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=20) bf_w'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.CurrDBGroupTbspFullAlertSummary', p_sql_statement => l_sql); 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.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'); mgmt_ip.add_element_definition ('DBMSG_CURRENT_DATABASE_GROUP_TBSPFULL_ALERT_SUMMARY', 'database_target_type', 'DBMSG_CURRENT_DATABASE_GROUP_TBSPFULL_ALERT_SUMMARY_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- CHART 2.4: Database Group Space Allocation -- -- Data Scope: Repository (Latest and Historical Data) -- Chart Type: Table -- Agent Version: -- 1) For 10.2 GC Agent show all columns -- 2) 10.1.0.4 DB Control Agent -- * Since tbspAllocation is only available in 10.2GC -- hide the 2 Avg and 2 Max columns, they're historical -- so they 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. The table will contain the following -- columns: -- Database Name -- Current Allocated Size (GB) -- Current Allocated Used (GB) -- Current Allocated Free (GB) -- Current Allocated Used (%) -- Avg Allocated Size (GB) -- Avg Allocated Used (GB) -- Max Allocated Size (GB) -- Max Allocated Used (GB) -- ------------------------------------------------------------ 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/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/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/tsize.min_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=''oracle_database'' OR dbs.member_target_type=''rac_database'') 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=''oracle_database'' OR dbs.member_target_type=''rac_database'') 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=''oracle_database'' OR dbs.member_target_type=''rac_database'') 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); -- Element Definition 3 - Current Tablespace Allocation and Space Usage l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(18); 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'); mgmt_ip.add_element_definition ('DBMSG_SPACE_USAGE_BY_DB', 'database_target_type', 'DBMSG_SPACE_USAGE_BY_DB_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- CHART 2.5: Database Open Alert Count for Tablespace Full Alert -- -- Data Scope: Repository (Latest) -- Chart Type: Table -- Agent Version: -- 1) For 10.2 GC Agent and 10.1.0.4 DB Control Agent -- show all columns -- 2) For Pre-10.1.0.4 DB Control Agent -- hide the 2 Free Space(MB) columns -- NOTE: test the oracle_database.xml meta version for this -- -- Description: This table will report on every databsae in -- the group. It will give warning and critical -- alert counts. The following columns will be shown: -- Database -- Critical Space Used(%) -- Warning Space Used(%) -- Critical Free Space(MB) -- Critical Free Space(MB) -- -- Queries: VIEW and BASE_TABLE -- ------------------------------------------------------------ l_sql := 'SELECT dbs.target_name AS DBNAME, NVL(round(su_c.num_su_alerts_crit,0),0) AS CRITICAL, NVL(round(su_w.num_su_alerts_warn,0),0) AS WARNING, NVL(round(bf_c.num_bf_alerts_crit,0),0) AS CRITICAL, NVL(round(bf_w.num_bf_alerts_warn,0),0) AS WARNING FROM ( SELECT member_target_guid AS target_guid , member_target_name AS target_name FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs, ( SELECT ac.target_guid, count(ac.alert_state) AS num_su_alerts_crit FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=25 GROUP BY ac.target_guid) su_c, ( SELECT ac.target_guid, count(ac.alert_state) AS num_su_alerts_warn FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''pctUsed'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=20 GROUP BY ac.target_guid) su_w, ( SELECT ac.target_guid, count(ac.alert_state) AS num_bf_alerts_crit FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=25 GROUP BY ac.target_guid) bf_c, ( SELECT ac.target_guid, count(ac.alert_state) AS num_bf_alerts_warn FROM mgmt$alert_current ac, (SELECT member_target_guid AS target_guid FROM mgmt$group_flat_memberships WHERE composite_target_guid=??EMIP_BIND_TARGET_GUID?? AND (member_target_type=''oracle_database'' OR member_target_type=''rac_database'')) dbs WHERE ac.target_guid=dbs.target_guid AND (ac.metric_name=''problemTbsp'' OR ac.metric_name=''problemTbsp10iDct'') AND ac.metric_column=''bytesFree'' AND decode(alert_state, ''Critical'', 25, ''Warning'', 20, 15)=20 GROUP BY ac.target_guid) bf_w WHERE su_c.target_guid(+) = dbs.target_guid AND su_w.target_guid(+) = dbs.target_guid AND bf_c.target_guid(+) = dbs.target_guid AND bf_w.target_guid(+) = dbs.target_guid'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.storage.reports.DBGroupFullAlertCount', p_sql_statement => l_sql); 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'); mgmt_ip.add_element_definition ('DBMSG_DATABASE_GROUP_TBSPFULL_ALERT_COUNT', 'database_target_type', 'DBMSG_DATABASE_GROUP_TBSPFULL_ALERT_COUNT_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'composite'; 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'); -- group space usage for last 7 days 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_sub_category_nlsid => 'DBMSG_SPACE_USAGE', p_category_nlsid => 'DBMSG_DATABASE', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_SPACE_USAGE_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); -- Current Database Space Usage Area Label/Value l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_CURRENT_DBGROUP_CUMULATIVE_SPACE_USAGE', 'database_target_type', 'DBMSG_CURRENT_DBGROUP_CUMULATIVE_SPACE_USAGE_DESC', 2, 2, null, null); -- Historical Database Space Usage Chart l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_HISTORICAL_GROUP_CUMULATIVE_SPACE_USAGE', 'database_target_type', 'DBMSG_HISTORICAL_GROUP_CUMULATIVE_SPACE_USAGE_DESC', 3, 2, null, null); -- Group Tablespace Allocation Space Usage By Database l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SPACE_USAGE_BY_DB', 'database_target_type', 'DBMSG_SPACE_USAGE_BY_DB_DESC', 4, 3, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_SEPARATOR', 'IPMSG_NO_TARGET_TYPE', '', 5, 4, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_SPACE_USAGE_TBSPFULL_ALERT_INSTRUCTION', 'database_target_type', '', 6, 5, null, null); -- Element 4 - Group Tablespace Full Allert Summary Label/Value l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_CURRENT_DATABASE_GROUP_TBSPFULL_ALERT_SUMMARY', 'database_target_type', 'DBMSG_CURRENT_DATABASE_GROUP_TBSPFULL_ALERT_SUMMARY_DESC', 7, 6, null, null); -- Element 5 - Group Tablespace Full Alert by Database l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_DATABASE_GROUP_TBSPFULL_ALERT_COUNT', 'database_target_type', 'DBMSG_DATABASE_GROUP_TBSPFULL_ALERT_COUNT', 8, 6, null, null); COMMIT; END; END; / SET DEFINE ON
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de