Rem
Rem $Header: storage_metric_setup.sql 14-jul-2005.13:37:23 rreilly Exp $
Rem
Rem storage_metric_setup.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      storage_metric_setup.sql - Storage Metrics
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem
Rem      !!!!!!! Please update UPGRADE script when updates are made. !!!!!!!
Rem
Rem      Must be run before storage_policy_setup.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    xshen       06/07/05 - xshen_bug-4411021
Rem    xchen       06/07/05 - bug 4411021 upgrade changes for beta 
Rem    rreilly     05/18/05 - 4378437 hide repository metrics
Rem    rreilly     05/18/05 - 4378424 fix snapshot to metric mapping 
Rem    rreilly     04/22/05 - make sure people update the upgrade scripts 
Rem    rreilly     03/07/05 - bug 4221764 set category prop for metrics 
Rem    rreilly     02/15/05 - version repository metrics 
Rem    rreilly     01/24/05 - add Storage Metrics
Rem    rreilly     12/13/04 - Created
Rem

-- SET ECHO ON
-- SET FEEDBACK 1
-- SET NUMWIDTH 10
-- SET LINESIZE 80
-- SET TRIMSPOOL ON
-- SET TAB OFF
-- SET PAGESIZE 100

SET DEFINE OFF

-- ==========================================================
--
-- Create Storage Metrics
--
-- ==========================================================

DECLARE

  l_sql                 VARCHAR2(1000);
  l_snapshotList        MGMT_SNAPSHOT_ARRAY;
  l_metricColList       MGMT_METRIC_COLUMN_ARRAY;
  l_categoryList        MGMT_CATEGORY_ARRAY;
  l_validIfDB           MGMT_VALIDIF_ARRAY;
  l_validIfDB92         MGMT_VALIDIF_ARRAY;
  l_10gR1MetaVersion    CONSTANT VARCHAR2(3) := '3.0';
  l_ASM10gR1MetaVersion CONSTANT VARCHAR2(3) := '2.0';

BEGIN

  -- All these metrics are storage metrics
  l_categoryList := MGMT_CATEGORY_ARRAY(
    MGMT_CATEGORY_OBJ.NEW(
      p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
      p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_STORAGE)); 

  -- Use this to register metrics at the DB level, not instance level
  l_validIfDB := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(
      p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB')
    )
  );

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Rollback Segments in SYSTEM Tablespace
  --    
  -- Metric Name: DB_RBS_IN_SYSTEM
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric is specific for the 'Rollback in SYSTEM
  --    Tablespace' Policy.  If the 'undo_management' init param
  --    is set to 'MANUAL' then this metric should return a row 
  --    for each rollback segment that is in the SYSTEM tablespace
  --    The rollback segment name will be returned.
  --    NOTE: Exclude default SYSTEM rollback segments
  --          Check undo_management='MANUAL' OR db version is pre-9i
  --    @BUG: currently do_management is only 
  --         available at the instance level (init params are being collected at the
  --         instance level), but for RAC rollback segs should be
  --         handled at the db level (rac_database).
  -- ----------------------------------------------------------
  
  l_sql := 'SELECT ' || 
              'r.rollname as RBS_NAME, ' ||
              'r.tablespace_name as TABLESPACE_NAME, ' ||
              's.target_guid as TARGET_GUID ' ||
           'FROM ' || 
              'mgmt_db_rollback_segs_ecm r, ' ||
              'mgmt_db_init_params_ecm p, ' ||
              'mgmt_ecm_gen_snapshot s ' ||
           'WHERE ' ||
              'r.tablespace_name = ''SYSTEM'' and ' ||
              'r.rollname != ''SYSTEM'' and ' ||
              'r.ecm_snapshot_id = s.snapshot_guid and ' ||
              's.is_current =  ''Y'' and ' ||
              '((s.target_type = ''rac_database'') or ' ||
                '(s.target_type = ''oracle_database'' and ' ||
                 'exists ( select * from mgmt_target_properties tp ' ||
                          'where s.target_guid = tp.target_guid and ' ||
                                'tp.property_name = ''RACOption'' and ' ||
                                'tp.property_value = ''NO''))) and ' ||
                'r.ecm_snapshot_id = p.ecm_snapshot_id (+) and ' ||
                'p.name (+) = ''undo_management'' and ' ||
                'upper(nvl(p.value,''MANUAL'')) = ''MANUAL'' ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'RBS_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'RBS_NAME',
      p_column_label_nlsid => 'RBS_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_dbconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_RBS_IN_SYSTEM',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Rollback Segment in SYSTEM Tablespace',
      p_metric_label_nlsid => 'DB_RBS_IN_SYSTEM_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_racconfig'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_RBS_IN_SYSTEM',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Rollback Segment in SYSTEM Tablespace',
      p_metric_label_nlsid => 'DB_RBS_IN_SYSTEM_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Recommended User Settings 
  --    
  -- Metric Name: DB_REC_USER_SETTINGS
  -- DB Version: All
  -- Agent Version: 10.1.0.2 +
  -- Description: 
  --    This metric is for the 'Users with Permanent Tablespaces
  --    as Temporary Tablespace' and 'Users with a System Tablespace
  --    as Default Tablespace'.
  --    The data in this metric is returned as User Name, 
  --    Problem Code and Tablespace Name.
  -- Problem Codes:
  --    1   Users with Permanent Tablespace as Temporary Tablespace
  --    2   Users with a System Tablespace as Default Tablespace
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               'u.user_name as USER_NAME, ' ||
               'u.problem_code as PROBLEM_CODE, ' ||
               'u.tablespace as TABLESPACE_NAME, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_recusersettings_ecm u, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               'u.ecm_snapshot_id = ss.snapshot_guid and ' ||
               'ss.is_current =  ''Y'' ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'USER_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'USER_NAME',
      p_column_label_nlsid => 'USER_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_REC_USER_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database User Settings',
      p_metric_label_nlsid => 'DB_REC_USER_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_REC_USER_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Recommended User Settings',
      p_metric_label_nlsid => 'DB_REC_USER_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Recommended Tablespace Settings
  --    
  -- Metric Name: DB_REC_TS_SETTINGS
  -- DB Version:  All
  -- Agent Version: 10gR1 (10.1.0.2 +)
  -- Description: 
  --    This metric supports four currently used policies.
  --    The data in this metric is returned as Tablespace Name,
  --    Problem Code, Value1 (NUMBER) and Value2 (NUMBER).
  -- Problem Codes:
  --    1   Tablespace Not Using Automatic Segment-Space Management
  --    2   Dictionary Managed Tablespace
  --    3   Tablespace Containing Rollback and Data Segments
  --    4   (Not Used) Tbsp has non-zero pctincrease
  --    5   (Not Used) Tbsp next extent not a multiple of init extent
  --    6   Deafult Temporary Tablespace set to a System Tablespace
  --    7   Default Permanent Tablespace set to a System Tablespace
  --
  -- @RAR For Translation: 
  --          VALUE1 should read 'Number of Rollback Segments'
  --          VALUE2 should read 'Number of Data Segments'    
  --      Since the only policy to use these is 
  --      Tablespaces Containing Rollback and Data Segments
  --      this should be okay.      
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               't.tablespace as TABLESPACE_NAME, ' ||
               't.problem_code as PROBLEM_CODE, ' ||
               't.value1 as VALUE1, ' ||
               't.value2 as VALUE2, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_rectssettings_ecm t, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               't.ecm_snapshot_id = ss.snapshot_guid and ' ||
               'ss.is_current =  ''Y'' ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE1',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE1',
      p_column_label_nlsid => 'VALUE1'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE2',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE2',
      p_column_label_nlsid => 'VALUE2'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_REC_TS_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Recommended Tablespace Settings',
      p_metric_label_nlsid => 'DB_REC_TS_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_REC_TS_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Recommended Tablespace Settings',
      p_metric_label_nlsid => 'DB_REC_TS_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Tablespace Not Using Automatic Segment-Space Management
  --    
  --    
  -- Metric Name: DB_TBSP_SEGSPACE_MGMT       
  -- DB Version: 9.2+
  -- Agent Version: 10gR1 (10.1.0.2 +)
  -- Description: 
  --    This metric supports four currently used policies.
  --    The data in this metric is returned as Tablespace Name,
  --    Problem Code, Value1 (NUMBER) and Value2 (NUMBER).
  -- Problem Codes:
  --    1   Tablespace Not Using Automatic Segment-Space Management
  --    2   Dictionary Managed Tablespace
  --    3   Tablespace Containing Rollback and Data Segments
  --    4   (Not Used) Tbsp has non-zero pctincrease
  --    5   (Not Used) Tbsp next extent not a multiple of init extent
  --    6   Deafult Temporary Tablespace set to a System Tablespace
  --    7   Default Permanent Tablespace set to a System Tablespace
  --
  -- @RAR For Translation: 
  --          VALUE1 should read 'Number of Rollback Segments'
  --          VALUE2 should read 'Number of Data Segments'    
  --      Since the only policy to use these is 
  --      Tablespaces Containing Rollback and Data Segments
  --      this should be okay.      
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               't.tablespace as TABLESPACE_NAME, ' ||
               't.problem_code as PROBLEM_CODE, ' ||
               't.value1 as VALUE1, ' ||
               't.value2 as VALUE2, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_rectssettings_ecm t, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               't.ecm_snapshot_id = ss.snapshot_guid and ' ||
               't.problem_code = 1 and ' ||
               'ss.is_current =  ''Y'' ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE1',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE1',
      p_column_label_nlsid => 'VALUE1'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE2',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE2',
      p_column_label_nlsid => 'VALUE2'));

  -- ORACLE DATABASE
  -- Use this to register metrics at the DB level, not instance level, for 9.2+ dbs only
  l_validIfDB92 := MGMT_VALIDIF_ARRAY(
    MGMT_VALIDIF_OBJ.NEW(p_catprop_1_choices => MGMT_CATEGORY_PROP_ARRAY('9iR2;10gR1;10gR2')),
    MGMT_VALIDIF_OBJ.NEW(p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB')));
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_TBSP_SEGSPACE_MGMT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB92,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Tablespace Not Using Automatic Segment-Space Management',
      p_metric_label_nlsid => 'DB_TBSP_SEGSPACE_MGMT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_TBSP_SEGSPACE_MGMT',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB92,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Tablespace Not Using Automatic Segment-Space Management',
      p_metric_label_nlsid => 'DB_TBSP_SEGSPACE_MGMT_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    DB Recommended Segment Settings
  --    
  -- Metric Name: DB_REC_SEGMENT_SETTINGS
  -- DB Version: All
  -- Agent Version: 10gR1 (10.1.0.2 +)
  -- Description: 
  --    This metric is used for two policies:
  --    'Non-System Data Segments in a System Tablespaces'
  --    'Segment in Dictionary Tablespace with Extent Growth Policy Violation'
  --    This metric will show the Tablespace Name, Segment Type, Lob Col, 
  --    Partition Name, Object Name and Object Owner.
  --    This policy Excludes CLUSTER obj types (obj_type=3)
  --    because reorg does not support them.
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               's.obj_owner || ''.'' || s.obj_name  as OBJECT, ' ||
               'DECODE(s.obj_type, 1, ''TABLE'', 2, ''INDEX'', 3, ''CLUSTER'', ' ||
                    ' ''UNHANDLED TYPE '' || s.obj_type) as OBJECT_TYPE, ' ||
               's.obj_owner as OBJECT_OWNER, ' ||
               's.obj_name as OBJECT_NAME, ' ||
               's.obj_partition as PARTITION, ' ||
               's.obj_lob_col as LOB_COL, ' ||
               'decode( s.segment_type, 1, ''TABLE'', 2, ''INDEX'', ' ||
                  '3, ''TABLE PARTITION'', 4, ''INDEX PARTITION'', ' ||
                  '5, ''IOT OVERFLOW'', 6, ''LOB INDEX'', 7, ''LOB COLUMN'', ' ||
                  '8, ''LOB PARTITION'', 9, ''TABLE SUBPARTITION'', ' ||
                  '10, ''INDEX SUBPARTITION'', 11, ''LOB SUBPARTITION'', ' ||
                  '12, ''CLUSTER'', 13, ''IOT OVERFLOW PARTITION'', ''UNKNOWN'') ' ||
                  'as SEGMENT_TYPE, ' ||
               's.tablespace as TABLESPACE_NAME, ' ||
               'decode(instr(s.obj_partition, '' ''), 1, ''&pname='', ''&pname=''||obj_partition) ' ||
                 'as PTN_PARAM, ' ||
               's.problem_code as PROBLEM_CODE, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_db_recsegmentsettings_ecm s, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               's.ecm_snapshot_id = ss.snapshot_guid and ' ||
               'ss.is_current =  ''Y'' and ' ||         
               's.obj_type != 3 ';

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'OBJECT',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'OBJECT',
      p_column_label_nlsid => 'OBJECT'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'OBJECT_TYPE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'OBJECT_TYPE',
      p_column_label_nlsid => 'OBJECT_TYPE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'OBJECT_OWNER',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'OBJECT_OWNER',
      p_column_label_nlsid => 'OBJECT_OWNER'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'OBJECT_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'OBJECT_NAME',
      p_column_label_nlsid => 'OBJECT_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PARTITION',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PARTITION',
      p_column_label_nlsid => 'PARTITION'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'LOB_COL',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'LOB_COL',
      p_column_label_nlsid => 'LOB_COL'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'SEGMENT_TYPE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'SEGMENT_TYPE',
      p_column_label_nlsid => 'SEGMENT_TYPE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'TABLESPACE_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'TABLESPACE_NAME',
      p_column_label_nlsid => 'TABLESPACE_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PTN_PARAM',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'PTN_PARAM',
      p_column_label_nlsid => 'PTN_PARAM'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'));
 
  -- ORACLE DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'oracle_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'oracle_database',
      p_metric_name => 'DB_REC_SEGMENT_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Recommended Segment Settings',
      p_metric_label_nlsid => 'DB_REC_SEGMENT_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  -- RAC DATABASE
  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'rac_database',
      p_snapshot_name => 'oracle_storage'));
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'rac_database',
      p_metric_name => 'DB_REC_SEGMENT_SETTINGS',
      p_type_meta_ver => l_10gR1MetaVersion,
      p_valid_if_list => l_validIfDB,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'Database Recommended Segment Settings',
      p_metric_label_nlsid => 'DB_REC_SEGMENT_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

  -- ----------------------------------------------------------
  -- Repository Metric
  --    ASM Instance Disk Group Settings
  --    
  -- Metric Name: DB_ASM_DISK_GROUP_SETTINGS
  -- ASM DB Versions: 10gR1, 10gR2 
  -- Agent Version: 10.2 DB osm metadata needed
  -- Description: 
  --    This metric is for ASM Database Disk Group Policies.
  -- Problem Codes:
  --    1 Disk Group Contains Disks of Significantly Different Sizes
  --    2 Disk Group Depends on External Redundancy and has Unprotected Disks
  --    3 Disk Group Contains Disks with Different Redundancy Attributes
  --    4 Disk Group with NORMAL or HIGH Redundancy has Mirrored or Parity Protected Disks
  -- ----------------------------------------------------------

  l_sql := 'SELECT ' ||
               'asm.DISK_GROUP as DISK_GROUP_NAME, ' ||
               'asm.problem_code as PROBLEM_CODE, ' ||
               'asm.value1_n as VALUE1_N, ' ||
               'ss.target_guid as TARGET_GUID ' ||
           'FROM ' ||
               'mgmt_osm_disk_group_ecm asm, ' ||
               'mgmt_ecm_gen_snapshot ss ' ||
           'WHERE ' ||
               'asm.ecm_snapshot_id = ss.snapshot_guid and ' ||
               'ss.is_current =  ''Y'' ';

  l_snapshotList := MGMT_SNAPSHOT_ARRAY(
    MGMT_SNAPSHOT_OBJ.NEW(
      p_target_type => 'osm_instance',
      p_snapshot_name => 'oracle_osm'));

  l_metricColList := MGMT_METRIC_COLUMN_ARRAY(
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'DISK_GROUP_NAME',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'DISK_GROUP_NAME',
      p_column_label_nlsid => 'DISK_GROUP_NAME'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'PROBLEM_CODE',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_TRUE,
      p_column_label => 'PROBLEM_CODE',
      p_column_label_nlsid => 'PROBLEM_CODE'),
    MGMT_METRIC_COLUMN_OBJ.NEW(
      p_column_name => 'VALUE1_N',
      p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
      p_is_key => MGMT_GLOBAL.G_FALSE,
      p_column_label => 'VALUE1_N',
      p_column_label_nlsid => 'VALUE1_N'));
 
  -- OSM INSTANCE
  MGMT_METRIC.CREATE_METRIC( 
      p_target_type => 'osm_instance',
      p_metric_name => 'DB_ASM_DISK_GROUP_SETTINGS',
      p_type_meta_ver => l_ASM10gR1MetaVersion,
      p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
      p_usage_type  => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
      p_metric_label => 'ASM Instance Disk Group Settings',
      p_metric_label_nlsid => 'DB_ASM_DISK_GROUP_SETTINGS_NLSID',
      p_is_repository => MGMT_GLOBAL.G_TRUE,
      p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY,
      p_source => l_sql,
      p_snapshot_name_list => l_snapshotList,
      p_category_list => l_categoryList,
      p_metric_column_list => l_metricColList);

  COMMIT;

END;
/

SET DEFINE ON