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