Rem drv: <migrate type="schema_upgrade" version="12.1.0.4" pos="metrics/metrics_schema_upgrade.sql-"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.4/metrics/metrics_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem metrics_types.sql
Rem
Rem Copyright (c) 2013, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.4/metrics/metrics_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.4/metrics/metrics_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      metrics_types.sql - pl/sql Types used for Adaptive thresholds
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    bram        01/16/14 - Add Types for Imp/exp template
Rem    bram        11/29/13  - Change Datasource_id to GUID
Rem    bram        11/24/13  - ADD em_bsln_thres_data_obj FOR static thresh 
Rem    bram        11/06/13 - Add EM_AT_THR_CR_VALUE_ARRAY
Rem    bram        06/24/13 - Add EM_AT_THR_VALUE_ARRAY
Rem    nqureshi    09/24/13 - add new attributes
Rem    bram        06/24/13 - Created
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.4/metrics/metrics_types.sql 
Rem    SQL_SHIPPED_FILE: 
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE 
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA

Rem
Rem  Type:
Rem    metbsln_interval_t
Rem
Rem  Description:
Rem    This relation is an interval or subinterval of a baseline.
Rem
Rem  Fields:
Rem    bsln_guid      - Globally unique identifier for the baseline
Rem    interval_begin - begin time of interval or subinterval
Rem    interval_end   - end time of interval or subinterval
Rem

create type metbsln_interval_t as object
   (bsln_guid      RAW(16)
   ,interval_begin date
   ,interval_end   date
   );
/

Rem
Rem  Type:
Rem    metbsln_interval_set
Rem
Rem  Description:
Rem    This is a set of intervals, or subintervals, of baselines.
Rem

create type metbsln_interval_set as table of metbsln_interval_t;
/

Rem
Rem  Type:
Rem    metbsln_observation_t
Rem
Rem  Description:
Rem    This relation is an observation of a data source.
Rem
Rem  Fields:
Rem    metric_item_id  - metric instance (<target,metric,key>) observed
Rem    bsln_guid         - unique baseline identifier
Rem    subinterval_code  - encoding of the subinterval of a baseline
Rem    obs_time        - time of observation
Rem    obs_value       - value observed

create type metbsln_observation_t as object
   (datasource_guid    raw(16)
   ,bsln_guid          raw(16)
   ,subinterval_code  raw(21)
   ,obs_time          date
   ,obs_value         number
   );
/

Rem
Rem  Type:
Rem    metbsln_observation_set
Rem
Rem  Description:
Rem    This is a set of observations of data sources.
Rem

create type metbsln_observation_set as table of metbsln_observation_t;
/


Rem
Rem  Type:
Rem    metbsln_statistics_t
Rem
Rem  Description:
Rem    An object attribute-column matched to metbsln_statistics
Rem

create type metbsln_statistics_t as object
   (bsln_guid         raw(16)
   ,datasource_guid   raw(16)
   ,compute_date      date   
   ,subinterval_code  raw(21)
   ,sample_count      number 
   ,average           number
   ,minimum           number
   ,maximum           number
   ,sdev              number
   ,pctile_25         number
   ,pctile_50         number
   ,pctile_75         number
   ,pctile_90         number
   ,pctile_95         number
   ,pctile_96         number
   ,pctile_97         number
   ,pctile_98         number
   ,est_sample_count  number
   ,est_slope         number
   ,est_intercept     number
   ,est_fit_quality   number
   ,est_pctile_99     number
   ,est_pctile_999    number
   ,est_pctile_9999   number
   );
/

Rem
Rem  Type:
Rem    metbsln_statistics_set
Rem
Rem  Description:
Rem    A set of statistics objects
Rem

create type metbsln_statistics_set as table of metbsln_statistics_t;
/

create type metbsnl_guid_key as object
   (metric_guid raw(32),
    key_id      NUMBER
   );
/

create type metbsnl_guid_key_array as table of metbsnl_guid_key;
/
create  type metbsln_stat_t as object
   (bsln_guid         raw(16)
   ,metric_item_id    number
   ,target_guid       raw(16)
   ,metric_guid       raw(16)
   ,key_value         varchar2(256)
   ,compute_date      date
   ,subinterval_code  raw(21)
   ,sample_count      number
   ,average           number
   ,minimum           number
   ,maximum           number
   ,sdev              number
   ,pctile_25         number
   ,pctile_50         number
   ,pctile_75         number
   ,pctile_90         number
   ,pctile_95         number
   ,est_sample_count  number
   ,est_slope         number
   ,est_intercept     number
   ,est_fit_quality   number
   ,est_pctile_99     number
   ,est_pctile_999    number
   ,est_pctile_9999   number
   );
/

Rem
Rem  Type:
Rem    metbsln_statistics_set
Rem
Rem  Description:
Rem    A set of statistics objects
Rem

create type metbsln_stat_set as table of metbsln_stat_t;
/


create type metbsln_th_param_t as object
   (target_guid       raw(16)
   ,metric_column_id  number
   ,metric_key_id     number
   );
/



create type metbsln_th_param_set as table of metbsln_th_param_t;
/


Rem
Rem  Type:
Rem    METBSLN_QCONF_METRIC_T
Rem
Rem  Description:
Rem    TO hold <metricSeeting tag IN basline quick configration mrs
Rem
Rem  Fields:
Rem    quick_configration_detail_id         - Id TO refer the details IN other TABLES
Rem    metric_group         - Metric GROUP OF the baselineable metric
Rem    metric_name        - Name OF Metric
Rem    threshold_type            - TYPE OF Threshold PCT_MAX/SIG_LVL
Rem    warn_thr                         - Warning threshold
Rem    crit_thr                   - Critical Threshold
Rem    insufficient_data_act                   - What TO DO IN  CASE OF NOT enough data TO calculate thresholds

CREATE OR REPLACE  type METBSLN_QCONF_METRIC_T AS OBJECT
   (
    metric_group               VARCHAR2(64)
    ,metric_name               VARCHAR2(64)
    ,threshold_type            VARCHAR2(12)
    ,warn_thr                  NUMBER
    ,crit_thr                  NUMBER
    ,insufficient_data_act     VARCHAR2(12)
    ,num_occurrence            NUMBER
    ,CONSTRUCTOR FUNCTION metbsln_qconf_metric_t
    RETURN SELF AS RESULT
   );
/

CREATE OR REPLACE  type BODY METBSLN_QCONF_METRIC_T AS 
CONSTRUCTOR FUNCTION metbsln_qconf_metric_t
    RETURN SELF AS RESULT
    AS 
    BEGIN 
  
      SELF.metric_group  :=metric_group  ;
      SELF.metric_name    :=metric_name  ;
      SELF.threshold_type  :=threshold_type ;
      SELF.warn_thr       := warn_thr ;
      SELF.crit_thr       :=           crit_thr;
      SELF.insufficient_data_act   :=insufficient_data_act   ;
      SELF.num_occurrence          :=num_occurrence;
      RETURN;
   END;
END;
/

create OR REPLACE  type METBSLN_QCONF_METRIC_SET as table of METBSLN_QCONF_METRIC_T;
/

Rem
Rem  type:
Rem    METBSLN_QCONF_CONFIG_T
Rem
Rem  Description:
Rem    TO STORE a Config row
Rem
Rem  Fields:
Rem    config_type                - Config TYPE OF FOR this quick config
Rem    resourcebundle_name        - RESOURCE Bundle FOR NLs
Rem    nls_id                     - NLS Id FOR NLs
Rem    default_text               - IN CASE NLS IS failed this will be displayed IN the UI
Rem    metbsln_qconf_metrics      - List OF Metrics
Rem
create OR REPLACE  type METBSLN_QCONF_CONFIG_T as object
   (config_Type          VARCHAR2(2)
    ,subinterval_key     VARCHAR2(2)
    ,interval_days       NUMBER(3)
    ,default_text        VARCHAR2(1024)
    ,resourcebundle_name VARCHAR2(512)
    ,nls_id              VARCHAR2(256)
    ,desc_nls_id              VARCHAR2(256)
     ,metbsln_qconf_metrics METBSLN_QCONF_METRIC_SET
    ,CONSTRUCTOR FUNCTION METBSLN_QCONF_CONFIG_T
    RETURN SELF AS RESULT
   );
/

CREATE OR REPLACE  type BODY METBSLN_QCONF_CONFIG_T AS 
CONSTRUCTOR FUNCTION METBSLN_QCONF_CONFIG_T
    RETURN SELF AS RESULT
    AS 
    BEGIN 
  
      SELF.config_Type  :=config_Type  ;
      SELF.subinterval_key :=subinterval_key;
      SELF.interval_days :=interval_days;
      SELF.default_text    :=default_text  ;
      SELF.resourcebundle_name  :=resourcebundle_name ;
      SELF.nls_id       := nls_id ;
      SELF.desc_nls_id       := desc_nls_id ;
      SELF.metbsln_qconf_metrics   :=metbsln_qconf_metrics   ;
      RETURN;
   END;
END;
/


create OR REPLACE  type METBSLN_QCONF_CONFIG_SET as table of METBSLN_QCONF_CONFIG_T;
/

Rem
Rem  type:
Rem    METBSLN_QCONF_T
Rem
Rem  Description:
Rem    TO STORE  A Quick configuration
Rem
Rem  Fields:
Rem    resourcebundle_name         - RESOURCE Bundle FOR NLs
Rem    nls_id                      - NLS Id FOR NLs
Rem    default_text                - IN CASE NLS IS failed this will be displayed IN the UI
Rem    p_metbsln_qconf_config_set  - List OF Configs
REM

create OR REPLACE  type METBSLN_QCONF_T as object
   (resourcebundle_name  VARCHAR2(512)
    ,nls_id              VARCHAR2(256)
    ,default_text        VARCHAR2(1024)
    ,p_metbsln_qconf_config_set METBSLN_QCONF_CONFIG_SET
    ,CONSTRUCTOR FUNCTION metbsln_qconf_t
    RETURN SELF AS RESULT
    );
/

CREATE OR REPLACE  type BODY METBSLN_QCONF_T AS 
CONSTRUCTOR FUNCTION METBSLN_QCONF_T
    RETURN SELF AS RESULT
    AS 
    BEGIN 
  
      SELF.resourcebundle_name  :=resourcebundle_name  ;
      SELF.nls_id    :=nls_id  ;
      SELF.default_text  :=default_text ;
      SELF.p_metbsln_qconf_config_set       := p_metbsln_qconf_config_set ;
      RETURN;
   END;
END;
/

create OR REPLACE  type METBSLN_QCONF_SET as table of METBSLN_QCONF_T;
/

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

CREATE OR REPLACE TYPE EM_AT_THR_VALUE_ARRAY IS VARRAY (14) OF VARCHAR2(64);
/

grant execute on EM_AT_THR_VALUE_ARRAY to sysman with GRANT OPTION;

create or replace noneditionable synonym SYSMAN.EM_AT_THR_VALUE_ARRAY for EM_AT_THR_VALUE_ARRAY;




CREATE OR REPLACE TYPE EM_BSLN_TH_DATA_OBJ as OBJECT (
   target_guid         RAW(16),
   metric_guid         RAW(16),
   metric_key_id       NUMBER,
   object_type         NUMBER,
   subinterval_key     VARCHAR2(64),
   is_processed        NUMBER,
   thr_warn_values     EM_AT_THR_VALUE_ARRAY,
   thr_cr_values       EM_AT_THR_VALUE_ARRAY,
   key_value           VARCHAR2(256)
);
/

GRANT EXECUTE ON EM_BSLN_TH_DATA_OBJ TO SYSMAN WITH GRANT OPTION;

CREATE OR REPLACE NONEDITIONABLE SYNONYM SYSMAN.EM_BSLN_TH_DATA_OBJ FOR EM_BSLN_TH_DATA_OBJ;


CREATE OR REPLACE TYPE   EM_BSLN_TH_DATA_ARR is table of  EM_BSLN_TH_DATA_OBJ;
/

GRANT EXECUTE ON EM_BSLN_TH_DATA_ARR TO SYSMAN WITH GRANT OPTION;

CREATE OR REPLACE NONEDITIONABLE SYNONYM SYSMAN.EM_BSLN_TH_DATA_ARR FOR EM_BSLN_TH_DATA_ARR;

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

CREATE OR REPLACE TYPE em_bsln_thres_data_obj as OBJECT (
 DATA_SET_GUID        RAW(16),
 METRIC_GUID          RAW(16),
 COLL_NAME            VARCHAR2(64),
 KEY_VALUE            VARCHAR2(256),
 EVAL_ORDER           NUMBER,
 NUM_OCCURENCES       NUMBER,
 FIXIT_JOB            VARCHAR2(256),
 WARNING_THRESHOLD    VARCHAR2(256),
 CRITICAL_THRESHOLD   VARCHAR2(256),
 WARNING_OPERATOR     NUMBER,
 CRITICAL_OPERATOR    NUMBER,
 KEY_PART1_VALUE      VARCHAR2(256),
 KEY_PART2_VALUE      VARCHAR2(256),
 KEY_PART3_VALUE      VARCHAR2(256),
 KEY_PART4_VALUE      VARCHAR2(256),
 KEY_PART5_VALUE      VARCHAR2(256),
 KEY_PART6_VALUE      VARCHAR2(256),
 KEY_PART7_VALUE      VARCHAR2(256)
 );
 /

 CREATE OR REPLACE TYPE   em_bsln_thres_data_arr is table of  em_bsln_thres_data_obj;
/

CREATE TYPE metbsln_imp_t AS OBJECT
   (metric_group_name      VARCHAR2(64)
   ,metric_col_name        VARCHAR2(64)
   ,key_value              VARCHAR2(256)
   ,threshold_method       varchar2(16)
   ,num_occurrences        NUMBER
   ,warninglvl             VARCHAR2(64)
   ,criticallvl           VARCHAR2(64)
   ,fail_action           VARCHAR2(16)
   );
/


CREATE TYPE metbsln_imp_obj AS TABLE OF metbsln_imp_t;
/


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;


CREATE OR REPLACE TYPE metbsln_static_imp_rec as OBJECT (
   metric_group_name      VARCHAR2(64),
   metric_col_name        VARCHAR2(64),
   key_value              VARCHAR2(256),
   subinterval_key     VARCHAR2(64),
   thr_warn_values     EM_AT_THR_VALUE_ARRAY,
   thr_cr_values       EM_AT_THR_VALUE_ARRAY
);
/

GRANT EXECUTE ON metbsln_static_imp_rec TO SYSMAN WITH GRANT OPTION;

CREATE OR REPLACE NONEDITIONABLE SYNONYM SYSMAN.metbsln_static_imp_rec FOR metbsln_static_imp_rec;

CREATE OR REPLACE TYPE   metbsln_static_imp_obj is table of  metbsln_static_imp_rec;
/

GRANT EXECUTE ON metbsln_static_imp_obj TO SYSMAN WITH GRANT OPTION;

CREATE OR REPLACE NONEDITIONABLE SYNONYM SYSMAN.metbsln_static_imp_obj FOR metbsln_static_imp_obj;


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;



