Rem drv:
Rem
Rem $Header: collections_views.sql 01-jul-2005.18:43:28 gsbhatia Exp $
Rem
Rem collections_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem collections_views.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem ktlaw 01/11/05 - add repmgr header
Rem rpinnama 11/22/04 - Dont use policies table in
Rem mgmt_metric_thresholds view
Rem rpinnama 09/24/04 - Add is_push to the view
Rem rpinnama 09/14/04 - rpinnama_obsolete_tables
Rem rpinnama 09/01/04 - Created
Rem
rem
rem PURPOSE
rem
rem The MGMT_METRIC_THRESHOLDS view contains thresholds for a collection
rem on a metric column basic, or on a per-key value basis for a metric
rem column
rem
rem COLUMNS
rem
rem see the definition for MGMT_METRICS_RAW
rem
rem COLL_NAME
rem
rem - The name of the collection collecting this metric
rem
rem WARNING_OPERATOR
rem
rem - The operator for the warning threshold. This is used in
rem the reporting environment to create a line on the graph
rem representing the warning threshold appropriately.
rem
rem 0 - GT
rem 1 - EQ
rem 2 - LT
rem 3 - LE
rem 4 - GE
rem 5 - CONTAINS
rem 6 - NE
rem 7 - MATCH : regular expression
rem
rem WARNING_THRESHOLD
rem
rem - value for the warning. Together with the warning operator
rem a graph line can be drawn to show the warning threshold.
rem
rem CRITICAL_OPERATOR
rem
rem - The operator for the critical threshold. This is used in
rem the reporting environment to create a line on the graph
rem representing the critical threshold appropriately.
rem
rem 0 - GT
rem 1 - EQ
rem 2 - LT
rem 3 - LE
rem 4 - GE
rem 5 - CONTAINS
rem 6 - NE
rem 7 - MATCH : regular expression
rem
rem CRITICAL_THRESHOLD
rem
rem - value for the critical severity. Together with the
rem critical operator, a graph line can be drawn to show the
rem critical severity threshold.
rem
rem NUM_OCCURENCES - the number of occurences of a warning/critical/clear
rem severity before a severity record is generated.
rem
rem NUM_WARNINGS - the number of consecutive times a metric value has
rem exceeded the warning threshold
rem
rem NUM_CRITICALS - the number of consecutive times a metric value has
rem exceeded the critical threshold
rem
rem EVAL_ORDER - the number representing the order for the evaluation of a condition
rem
rem FIXIT_JOB - the fixit job associated with this metric
rem
rem NOTES
rem
rem It may make sense in the future to add a date column which represents
rem the last collection time for the long string. This will provide the
rem means to determine whether or not the string is 'orphaned'.
rem
--
-- This view exists for backward compatibility reasons.
-- NOTE: There is no "$" in the view name because this view is created
-- with the intent of being used as a table for all practical purposes
--
CREATE OR REPLACE VIEW mgmt_metric_thresholds
(target_guid, metric_guid, coll_name, key_value, key_operator,
is_push, warning_operator, warning_threshold,
critical_operator, critical_threshold,
num_occurences, num_warnings, num_criticals,
eval_order, fixit_job, message, message_nlsid)
AS
SELECT pa.object_guid, pa.policy_guid, pa.coll_name, pac.key_value, pac.key_operator,
pac.is_push, pac.condition_operator, pacp.warn_threshold,
pac.condition_operator, pacp.crit_threshold,
pac.num_occurrences, 0, 0,
pac.eval_order, pac.fixit_job, pac.message, pac.message_nlsid
FROM mgmt_policy_assoc pa,
mgmt_policy_assoc_cfg pac,
mgmt_policy_assoc_cfg_params pacp
WHERE pa.policy_guid = pac.policy_guid
AND pa.object_guid = pac.object_guid
AND pa.coll_name = pac.coll_name
AND pac.policy_guid = pacp.policy_guid
AND pac.object_guid = pacp.object_guid
AND pac.coll_name = pacp.coll_name
AND pac.key_value = pacp.key_value
AND pac.key_operator = pacp.key_operator
AND pa.object_type = 2
AND pa.policy_type = 1
AND pacp.param_name = ' ';
rem PURPOSE
rem Contains details of the collections set up for a metric.
rem
rem COLUMNS
rem
rem TARGET_GUID - the GUID of the target associated with the collection
rem
rem METRIC_GUID - the GUID of the metric associated with the collection
rem
rem COLL_NAME - The name of the collection
rem
rem IS_REPOSITORY - flag to indicate whether this is a repository side
rem collection.A repository side collection is associated
rem with a metric specified in MGMT_METRICS and
rem has a PL/SQL evaluation procedure that is responsible for
rem calculating the metric values.
rem
rem STORE_METRIC - flag to indicate whether values returned by the
rem collection should be stored in the MGMT_METRIC_RAW.
rem 'Y' - STORE
rem 'N' - DO NOT STORE
rem
rem SCHEDULE - all repository side collections are scheduled to run by
rem DBMS JOBs (collection workers). TheDBMS JOB calls the
rem evaluation procedure (which is specified in the MGMT_METRICS table)
rem when it is scheduled to run. This column specifies the number of
rem minutes between successive runs of the collection
rem
rem SCHEDULE_EX - the schedule used for non-repository collections
rem
rem LAST_COLLECTED_TIMESTAMP - the time when the evaluation procedure was
rem last run
rem
rem STATUS_MESSAGE - a message indicating success/failure of the evaluation
rem procedure
rem
rem SUSPENDED - flag to indicate whether the collection is suspended
rem 0 => NOT SUSPENDED
rem 1 => SUSPENDED
rem
--
-- This view exists for backward compatibility reasons.
-- NOTE: There is no "$" in the view name because this view is created
-- with the intent of being used as a table for all practical purposes
--
CREATE OR REPLACE VIEW mgmt_metric_collections
(target_guid, metric_guid, coll_name,
is_repository, store_metric, schedule,
schedule_ex, last_collected_timestamp, status_message, suspended)
AS
SELECT c.object_guid, cmt.metric_guid, c.coll_name,
0, DECODE(c.store_metric, 1, 'Y', 'N'), c.interval,
c.schedule_ex, cmt.last_collected_timestamp,
cmt.status_message, DECODE(c.is_enabled, 1, 0, 1)
FROM mgmt_collections c,
mgmt_collection_metric_tasks cmt
WHERE c.object_guid = cmt.target_guid
AND c.coll_name = cmt.coll_name
AND c.object_type = 2;
--
-- This view exists for backward compatibility reasons.
-- NOTE: There is no "$" in the view name because this view is created
-- with the intent of being used as a table for all practical purposes
--
CREATE OR REPLACE VIEW mgmt_collection_properties
(target_guid, metric_guid, coll_name,
property_name, property_value)
AS
SELECT object_guid, metric_guid, coll_name,
property_name, property_value
FROM mgmt_coll_item_properties
WHERE object_type = 2;