Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\reports\db_outofbox

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\reports\db_outofbox_config.sql

Rem drv: <migrate type="data_upgrade" version="10.2.0.0" condition="EM_REPOS_MODE=CENTRAL"/>
Rem
Rem $Header: db_outofbox_config.sql 28-nov-2006.06:33:46 mnihalan Exp $
Rem
Rem db_outofbox_config.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      db_outofbox_config.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    chyu        05/13/06 - RCU Compliant 
Rem    lgloyd      06/24/05 - normalize category names 
Rem    lgloyd      05/10/05 - fix param type 
Rem    xshen       03/21/05 - Add comments
Rem    xshen       02/14/05 - change to use public views 
Rem    xshen       02/10/05 - support translation of row data 
Rem    xshen       02/03/05 - xshen_config_report
Rem    xshen       02/02/05 - 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_current_row   NUMBER := 1;
    l_current_order NUMBER := 1;
  BEGIN

-- Create DB Config Report Definition
     l_target_types := MGMT_IP_TARGET_TYPES();
     l_target_types.extend(1);
     l_target_types(1) := 'oracle_database';

l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                => 'DBCONFIG_REPORT_TITLE',
      p_description_nlsid          => 'DBCONFIG_REPORT_DESC',
      p_owner                      => 'SYSMAN',
      p_category_nlsid             => 'IPMSG_DEPLOYMENT_AND_CONFIGURATION_CATEGORY', 
      p_sub_category_nlsid         => 'IPMSG_ORACLE_DATABASE_CONFIGURATION_CATEGORY',
      p_late_binding_target_types  => l_target_types,
      p_late_binding_multi_targets => 0,
      p_show_table_of_contents     => 0,
      p_pack_name                  => 'db_config',
      p_system_report              => 1);

-- Element Definition - Instance Information
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Label Value Pairs
    -- Description: This section will report on how inst is configured
    --              Columns:
    --                  Option 
    --                  Selected [TRUE|FALSE]
    -- ------------------------------------------------------------

-- Element Definition: Instance Information
    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.rprt.DBConfigCommonMsg');
    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 o.database_name "DATABASE_NAME",
                o.characterset "CHARACTERSET",
                o.instance_name "INSTANCE_NAME",
                o.national_characterset "NATIONAL_CHARACTERSET",
                o.global_name "GLOBAL_NAME",
                o.log_mode "LOG_MODE",
                o.banner "BANNER",
                o.default_temp_tablespace "DEFAULT_TEMP_TABLESPACE"
          from mgmt$db_dbninstanceinfo o
         where o.target_guid = :1 '';

open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_INSTANCE_INFO',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_INSTANCE_INFO_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 1 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_INSTANCE_INFO',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_INSTANCE_INFO',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Database Options - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on what options are 
    --              currently valid for a database.
    --              Columns:
    --                  Option 
    --                  Selected [TRUE|FALSE]
    -- TBD: Until framework supports translating row DATA (communicated
    --      with Leslie that it can be done) we use decode in English 
    --      for option names.
    -- ------------------------------------------------------------

-- Element Definition: Database Options   
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(8);
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle',
      'oracle.sysman.db.rsc.rprt.DBConfigCommonMsg');
    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 o.name as OPTION_NAME, 
                      decode(o.selected, ''''TRUE'''', 15, null) as CONFIGURED
                 from mgmt$db_options o
                where o.target_guid = :1
                  order by name asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
    l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns',
       '0');   
    l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn',
       '1');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_OPTIONS',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_OPTIONS_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 2 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_OPTIONS',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_OPTIONS',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Initialization Parameters - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all initialization  
    --              parameters for a database.
    --              Columns:
    --                  Parameter
    --                  Value 
    --                  Default
    -- ------------------------------------------------------------

-- Element Definition: Initialization Parameters
    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.rprt.DBConfigCommonMsg');
    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 o.name as PARAMETER_COL,
                      o.value as VALUE_COL
                 from mgmt$db_init_params o
                where o.target_guid = :1
                  and o.isdefault = ''''FALSE''''
                  order by name asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_INITPARAMS',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_INITPARAMS_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 3 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_INITPARAMS',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_INITPARAMS',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Controlfiles - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all controlfile  
    --              configuration data for a database.
    --              Columns:
    --                  File Name
    --                  Status
    --                  Creation Date
    --                  Modification Date
    --                  OS Storage Entity
    -- ------------------------------------------------------------

-- Element Definition: Controlfiles
    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.rprt.DBConfigCommonMsg');
    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 o.file_name as FILE_NAME_COL,
                      o.status as STATUS_COL,
                      o.creation_date as CREATION_DATE_COL,
                      o.mod_date as MOD_DATE,
                      o.os_storage_entity as OS_STORAGE_ENTITY
                 from mgmt$db_controlfiles o
                where o.target_guid = :1
                  order by file_name asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_CONTROLFILES',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_CONTROLFILES_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 3 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_CONTROLFILES',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_CONTROLFILES',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Tablespaces - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all tablespaces  
    --              configuration data for a database.
    --              Columns:
    --                  Tablespace Name
    --                  Contents
    --                  Status
    --                  Extent Management
    --                  Allocation Type
    --                  Segment Space Management
    --                  Logging
    --                  Initial Extent Size
    --                  Next Extent
    --                  Increment By
    --                  Maximum Extents
    --                  Minimum Extents
    --                  Minimum Extent Length
    --                  Block Size
    --                  Big File
    -- ------------------------------------------------------------

-- Element Definition: Tablespaces 
    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.rprt.DBConfigCommonMsg');
    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 o.tablespace_name as TABLESPACE_NAME,
                      o.status as STATUS_COL,
                      o.contents as TYPE_COL,
                      o.extent_management as EXTENT_MANAGEMENT,
                      o.allocation_type as ALLOCATION_COL,
                      o.segment_space_management as SEGMENT_SPACE_MANAGEMENT,
                      o.logging as ENABLE_LOGGING_COL,
                      TO_CHAR(NVL(o.initial_ext_size/1024,0),''''99G999G990'''') as INIT_EXT_K_COL,
                      TO_CHAR(NVL(o.next_extent/1024,0),''''99G999G990'''') as NEXT_SIZE_K_COL,
                      TO_CHAR(NVL(o.increment_by,0),''''999999990D00'''') as PCT_INCREASE,
                      DECODE(o.max_extents,null,''''UNLIMITED'''',TO_CHAR(max_extents)) as MAX_EXTENTS,
                      o.min_extents as MIN_EXTENTS,
                      TO_CHAR(NVL(o.min_extlen/1024,0),''''99G999G990'''') as MIN_EXTLEN_K,
                      o.block_size as BLOCK_SIZE_COL,
                      TO_CHAR(NVL(o.bigfile,''''NA'''')) as BIGFILE
                 from mgmt$db_tablespaces o
                where o.target_guid = :1
                  order by tablespace_name asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_TABLESPACES',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_TABLESPACES_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 4 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_TABLESPACES',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_TABLESPACES',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Datafiles - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all tablespaces  
    --              configuration data for a database.
    --              Columns:
    --                  File Name
    --                  Tablespace Name
    --                  Status
    --                  Autoextensible
    --                  File Size
    --                  Max File Size
    --                  OS Storage Entity
    -- ------------------------------------------------------------

-- Element Definition: Datafiles
    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.rprt.DBConfigCommonMsg');
    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 o.file_name as FILE_NAME_COL,
                      o.tablespace_name as TABLESPACE_NAME,
                      o.status as STATUS,
                      o.autoextensible as AUTOEXTENSIBLE,
                      TO_CHAR(NVL(o.file_size/1024/1024,0),''''99999990D000'''') as SIZE_M_COL,
                      DECODE(o.max_file_size,0,''''UNLIMITED'''',TO_CHAR(NVL(max_file_size/1024/1024,0),''''99999990D000'''')) as MAX_FILE_SIZE_M_COL,
                      TO_CHAR(o.increment_by,''''999999990D000'''') as INCREMENT_BLOCKS_COL,
                      o.os_storage_entity as OS_STORAGE_ENTITY
                 from mgmt$db_datafiles o
                where o.target_guid = :1
                  order by tablespace_name asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_DATAFILES',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_DATAFILES_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 5 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_DATAFILES',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_DATAFILES',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Redo Log Groups - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all datafiles
    --              configuration data for a database.
    --              Columns:
    --                  Group Num
    --                  Status
    --                  Members
    --                  File Name
    --                  Log Size
    --                  OS Storage Entity
    --                  Thread Num
    -- ------------------------------------------------------------

-- Element Definition: Datafiles
    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.rprt.DBConfigCommonMsg');
    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 o.group_num as GROUP_NUM_COL,
                      o.status as STATUS,
                      o.members as MEMBERS,
                      o.file_name as FILE_NAME,
                      TO_CHAR((logsize/1024), ''''99G999G990'''') as SIZE_K_COL,
                      o.thread_num as THREAD_NUM,
                      o.os_storage_entity as OS_STORAGE_ENTITY
                 from mgmt$db_redologs o
                where o.target_guid = :1
                  order by group_num asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_REDOLOGS',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_REDOLOGS_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 6 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_REDOLOGS',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_REDOLOGS',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

l_current_order := l_current_order + 1;
     l_current_row   := l_current_row + 1;

-- Element Definition - Rollback Segments - table
    -- ------------------------------------------------------------
    -- Data Scope:  Repository (Current Snapshot Data)
    -- Chart Type:  Table
    -- Description: This table will report on all rollback segs  
    --              configuration data for a database.
    --              Columns:
    --                  Segment Name
    --                  Status
    --                  Tablespace Name
    --                  Extents
    --                  Size
    --                  Initial Size
    --                  Next Size
    --                  Max Extents
    --                  Min Extents
    --                  PCT Increase
    --                  Optimal Size
    --                  Average Active
    --                  Wraps
    --                  Shrinks
    --                  Average Shink
    --                  HWM Size
    -- ------------------------------------------------------------

-- Element Definition: Datafiles
    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.rprt.DBConfigCommonMsg');
    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 o.rollname as ROLLNAME,
                      o.status as STATUS,
                      o.tablespace_name as TABLESPACE_NAME,
                      o.extents as EXTENTS,
                      TO_CHAR(NVL(o.rollsize/1024/1024,0),''''99G999G990D900'''') as SIZE_M_COL,
                      TO_CHAR(NVL(o.next_size/1024,0),''''99G999G990'''') as NEXT_SIZE_K_COL,
                      o.maximum_extents as MAXIMUM_EXTENTS,
                      o.minimum_extents as MINIMUM_EXTENTS,
                      o.pct_increase as PCT_INCREASE,
                      o.optsize as OPTSIZE,
                      o.aveactive as AVEACTIVE,
                      o.wraps as WRAPS,
                      o.shrinks as SHRINKS,
                      o.aveshrink as AVESHRINK,
                      TO_CHAR(NVL(o.hwmsize/1024/1024,0),''''99G999G990D900'''') as HWMSIZE_M
                 from mgmt$db_rollback_segs o
                where o.target_guid = :1
                  order by rollname asc'';
           open result_cursor_out for query_string using tgt_guid_in(1);
         end; 
        end;');

l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget',
       'true');
    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
       'true');
    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn',
       '0');
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
       'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder',
       'ascending');   
   
    mgmt_ip.add_element_definition
       (p_element_name_nlsid => 'DBCONFIG_ROLLBACKSEGS',
        p_element_type_nlsid => 'database_target_type',
        p_description_nlsid  => 'DBCONFIG_ROLLBACKSEGS_DESC',
        p_element_class_name => 'oracle.sysman.eml.ip.render.elem.TableRenderController',
        p_internal_use_only  => 1,
        p_default_parameters => l_param_values
       );

-- Add Element 7 to report.
     l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid        => l_report_guid,
      p_element_name_nlsid => 'DBCONFIG_ROLLBACKSEGS',
      p_element_type_nlsid => 'database_target_type',
      p_header_nlslid      => 'DBCONFIG_ROLLBACKSEGS',
      p_element_order      => l_current_order,
      p_element_row        => l_current_row, 
      p_parameters         => l_param_values,
      p_targets            => null);

-- End
    COMMIT;

END;

END;
/

SET DEFINE ON

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de