Rem drv:
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/eml/eml_pkgdefs.sql /st_emdbsa_11.2/9 2010/02/08 10:59:07 yemeng Exp $
Rem
Rem eml_pkgdefs.sql
Rem
Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem eml_pkgdefs.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem yemeng 02/04/10 - optimize the metric sql
Rem mpawelko 03/13/09 - XbranchMerge 6699755 add target_type so index is used; XbranchMerge 7562407 use target_type in getASMTargetName
Rem manaraya 02/23/09 - Bug 7931482
Rem ychan 12/22/08 - XbranchMerge ychan_bug-7562377 from
Rem st_emdbgc_10.2.0.1.0
Rem ychan 11/24/08 - Fix bug 7562377
Rem yemeng 09/25/08 - XbranchMerge yemeng_bug-7293143_gc from
Rem st_emdbgc_10.2.0.1.0
Rem yemeng 09/12/08 - bug 7293143
Rem dchakumk 03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from
Rem st_emdbsa_11.1
Rem kramarat 01/18/08 - SIHA Integration
Rem mpawelko 01/18/08 - bug 6699755 add target_type so index is used
Rem kramarat 12/10/07 - SI HA Integration
Rem ychan 06/19/07 - XbranchMerge ychan_bug-5954219 from st_emdbsa_11.1
Rem ychan 06/04/07 - Improve perf
Rem chyu 06/28/05 - New repmgr header impl
Rem pbantis 03/09/05 - Added HA methods for getting values from db.
Rem pbantis 02/13/05 - Update for HA area.
Rem jochen 12/14/04 - Allow ASM to use severity functions
Rem kramarat 12/09/04 - add new procedures
Rem swexler 11/16/04 - restructure
Rem mnihalan 06/24/04 - Change the getStgPerfValues
Rem jsoule 11/05/03 - dates are DATEs. period.
Rem vchao 10/29/03 - Host CPU Utilization %
Rem jsoule 10/27/03 - add OpenMode dynamic property
Rem jsoule 10/25/03 - compute date from key for alertLog
Rem hsu 10/03/03 - use page scan rate
Rem ychan 07/29/03 - Fix bug 3070417
Rem hying 07/01/03 - Fix bug 2988520, archAreaCount
Rem jochen 06/25/03 - wastedspace returns eod row
Rem vchao 04/03/03 - Bug 2768204. Support metric versioning
Rem jochen 03/28/03 - Add Storage Performance link
Rem hsu 12/26/02 - rm unused db group proc
Rem jsoule 01/31/03 - fix db group wait 7-day chart
Rem swexler 11/19/02 - add response severity
Rem jsoule 11/05/02 - add constants
Rem jsoule 11/07/02 - add to_tz helper
Rem jsoule 10/30/02 - add user created callback for default metric preferences
Rem yfeng 10/22/02 - Remove one procedure
Rem yfeng 10/22/02 - Add getResponseTime for SQL Response time
Rem jsoule 10/23/02 - change get_metric_state_at's cursor return
Rem jsoule 10/18/02 - add routine to get top alert instances data
Rem jsoule 10/02/02 - add routine to get top wait instances data
Rem ychan 09/13/02 - Add db group func
Rem vchao 08/21/02 - Add getDupSqlCountValue for sql reuse
Rem jsoule 08/27/02 - modify alert log return data
Rem jsoule 07/12/02 - remove support for the response metric
Rem rpatti 06/13/02 - update for new avail states
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem jsoule 04/23/02 - return non-NULL for latest alert log entry.
Rem rpatti 04/10/02 - show availability details
Rem jsoule 04/04/02 - return last known alert log tail.
Rem tjaiswal 04/01/02 - Misc group fixes
Rem jsoule 03/29/02 - .
Rem jsoule 03/26/02 - fix compilation errors.
Rem jsoule 03/19/02 - add db group page mods for alert log severity changes.
Rem jsoule 03/19/02 - change alert log severity semantics.
Rem jsoule 02/13/02 - rely on Instance Performance metric.
Rem edemembe 02/01/02 - database package fix
Rem jsoule 02/04/02 - .
Rem jsoule 02/04/02 - fix typo.
Rem rreilly 01/30/02 - use memUsedPct for getHostSeveritys
Rem jsoule 01/31/02 - fix response baselines and sql severities.
Rem jsoule 01/18/02 - pull instance data from properties instead of a metric.
Rem rpatti 01/07/02 - more baslined data
Rem jsoule 01/04/02 - fix bad sql counter.
Rem rpatti 01/03/02 - move member query out of group main page
Rem edemembe 01/08/02 - Metrics are now target independent
Rem rpatti 12/27/01 - inventory changes for db group pages
Rem ancheng 12/20/01 - add fields
Rem jsoule 12/18/01 - eliminate string-number translation errors.
Rem jsoule 12/14/01 - update response time distribution cursor.
Rem rpatti 12/12/01 - fix query
Rem rpatti 12/11/01 - fix condition error
Rem rpatti 12/06/01 - add db group homepage queries
Rem jsoule 11/06/01 - tune homepage queries.
Rem rreilly 10/11/01 - use memUsedPct instead of memfreePct
Rem groyal 10/05/01 - Fix memoryUsagePct
Rem rpatti 10/02/01 - fix host related metric column name
Rem mpawelko 09/25/01 - Pass back severity for avgResp
Rem rpatti 09/26/01 - use metric_guid instead of metric name, column
Rem rpatti 09/10/01 - use consistent timestamp formats
Rem mpawelko 09/13/01 - Don't return null values for strings either
Rem mpawelko 09/10/01 - Use target_guid instead of target_name, type
Rem mpawelko 09/06/01 - Don't return 0 values if null
Rem jsoule 09/05/01 - fix dummy condition.
Rem mpawelko 08/31/01 - Get host status in getHostValues
Rem mpawelko 08/30/01 - Add support for getting host status
Rem jsoule 08/29/01 - update problem sql queries.
Rem groyal 08/25/01 - Initialize variables
Rem jsoule 08/03/01 - rename column of mgmt_sql_evaluation.
Rem hjchu 08/09/01 - change logic to count alert log entries
Rem hjchu 07/30/01 - change table names from smp_vdt to mgmt_
Rem qsong 07/19/01 - fix syntax error for aCursorVdtCurrentMetricsTbsp
Rem hjchu 07/19/01 - fix alert log SQLs
Rem jsoule 06/04/01 - update sql evaluation count
Rem groyal 05/30/01 - Improve performance
Rem glavash 05/29/01 - change column used for active sessions
Rem hjchu 05/11/01 - Created
Rem
CREATE OR REPLACE PACKAGE emd_database AS
-- Package level Type Definition
TYPE cursorType IS REF CURSOR;
k_metric_status_unconfigured CONSTANT NUMBER := -2;
k_metric_status_inaccessible CONSTANT NUMBER := -1;
/*
* set_default_metric_preferences - set the default metrics to show up in the
* summary for a given user
*
* p_user_name - the user being modified
* p_callback_type - type of modification; may be one of:
* MGMT_USER.USER_CREATED_CALLBACK
* MGMT_USER.USER_DROPPED_CALLBACK
*/
PROCEDURE set_default_metric_preferences(p_user_name IN VARCHAR2,
p_callback_type IN NUMBER);
/*
Get the db site map related info. This procedure returns all the relevant
information for a given database target name and target type.
The values returned as OUT parameters are used for display in the
dbsitemap page
*/
procedure getDBSiteMapInfo(
targetName IN VARCHAR2,
targetType IN VARCHAR2,
dbSitemapInfo OUT SMP_EMD_DBSITEMAP_OBJ);
procedure getCollectionTimestamp(
targetGuid IN VARCHAR2,
lastUpdatedTime OUT VARCHAR2);
function isArchiveLogMode(
targetGuid IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2) return NUMBER;
procedure getDbStringValues(
targetGuid IN VARCHAR2,
instanceName OUT VARCHAR2,
instanceStartTime OUT DATE,
instanceVersion OUT VARCHAR2,
oracleHome OUT VARCHAR2,
cpuCount OUT NUMBER,
openMode OUT VARCHAR2);
procedure getDbStringValues(
targetGuid IN VARCHAR2,
instanceName OUT VARCHAR2,
instanceStartTime OUT DATE,
instanceVersion OUT VARCHAR2);
procedure getDbValues(
targetGuid IN VARCHAR2,
targetType IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
archAreaPctUsed OUT NUMBER,
dumpAreaPctUsed OUT NUMBER);
procedure getKeyProfile(
targetGuid IN VARCHAR2,
targetType IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
keyProfileCount OUT NUMBER,
keyProfileEnable OUT VARCHAR2,
autoImplEnable OUT VARCHAR2);
FUNCTION extract_alertLog_timestamp(key_value IN VARCHAR2) RETURN DATE;
procedure getDbAlertLogSeverity(
targetGuid IN VARCHAR2,
targetType IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
alertLogTimestamp OUT DATE,
alertLogSeverity OUT NUMBER);
procedure getDbCountValues(
targetGuid IN VARCHAR2,
targetType IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
sqlCount OUT NUMBER,
archCount OUT NUMBER,
badArchCount OUT NUMBER,
topSqlCount OUT NUMBER,
tbspCount OUT NUMBER);
procedure getDupSqlCountValue(
targetGuid IN VARCHAR2,
dupSqlCount OUT NUMBER);
k_sql_response_category CONSTANT VARCHAR2(64) := 'sql_response';
k_sql_response_metric CONSTANT VARCHAR2(64) := 'time';
procedure getDbSeveritys(
targetGuid IN VARCHAR2,
target_type_in IN VARCHAR2,
meta_ver IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
sqlSeverity OUT NUMBER,
tbspSeverity OUT NUMBER,
archAreaSeverity OUT NUMBER,
dumpAreaSeverity OUT NUMBER,
responseSeverity OUT NUMBER);
procedure getHostName(
targetGuid IN VARCHAR2,
hostName OUT VARCHAR2,
hostGuid OUT VARCHAR2);
procedure getListenerName(
targetGuid IN VARCHAR2,
hostName IN VARCHAR2,
listenerName OUT VARCHAR2);
procedure getMTTR(
targetGuid IN VARCHAR2,
MTTR OUT NUMBER);
procedure getAsmTargetName(
targetName IN VARCHAR2,
targetType IN VARCHAR2,
hostName IN VARCHAR2,
asmTargetName OUT VARCHAR2);
procedure getSihaInfo(
v_targetType IN VARCHAR2,
v_targetGuid IN VARCHAR2,
v_dbRegistered OUT VARCHAR2);
procedure getStgPerfValues(
targetGuid IN VARCHAR2,
target_type_in IN VARCHAR2,
instanceVersion IN VARCHAR2,
cat_prop_1 IN VARCHAR2,
cat_prop_2 IN VARCHAR2,
cat_prop_3 IN VARCHAR2,
cat_prop_4 IN VARCHAR2,
cat_prop_5 IN VARCHAR2,
stgPerfCount OUT NUMBER,
stgPerfSeverity OUT NUMBER);
function getTargetGuid(
targetName IN VARCHAR2,
targetType IN VARCHAR2) return VARCHAR2;
/*
Get the sitemap info that is common to rac and database.
*/
procedure getDBSiteMapCommonInfo(
targetName IN VARCHAR2,
targetType IN VARCHAR2,
dbSitemapInfo OUT SMP_EMD_DBSITEMAP_COMMON_OBJ);
function getTimeFormat(targetGuid IN VARCHAR2) return VARCHAR2;
function getDbInstanceVersion(targetGuid IN VARCHAR2) return VARCHAR2;
procedure getDbSize(
targetGuid IN VARCHAR2,
dbSize OUT NUMBER);
procedure getDGRole(
targetGuid IN VARCHAR2,
dgRole OUT VARCHAR2);
procedure getPrmyDetails(
targetGuid IN VARCHAR2,
prmyEMName OUT VARCHAR2,
prmyType OUT VARCHAR2);
procedure getBackupValues(
targetGuid IN VARCHAR2,
backupStatus OUT VARCHAR2,
backupTime OUT DATE);
procedure getFlashRecoveryValuesFromDb(
timeFormat IN VARCHAR2,
flashbackTime OUT DATE,
recoveryPct OUT NUMBER,
flashRecoveryArea OUT VARCHAR2);
procedure getFlashRecoveryValuesFromRep(
targetName IN VARCHAR2,
targetType IN VARCHAR2,
timeFormat IN VARCHAR2,
flashbackTime OUT DATE,
recoveryPct OUT NUMBER,
flashRecoveryArea OUT VARCHAR2);
procedure getLogModeAndFlashbackFromDb(
targetGuid IN VARCHAR2,
logMode OUT VARCHAR2,
flashbackOn OUT VARCHAR2);
procedure getLogModeAndFlashbackFromRep(
targetGuid IN VARCHAR2,
targetName IN VARCHAR2,
targetType IN VARCHAR2,
instanceVersion IN VARCHAR2,
logMode OUT VARCHAR2,
flashbackOn OUT VARCHAR2);
/*
* to_tz - translate a date from one timezone to another
*
* p_source_date - the original date
* p_source_tz_delta - the timezone delta for the region of the original
* date
* p_destination_tz_delta - the timezone delta for the region of the final
* date
*
* returns the date relative to the destination region
*/
FUNCTION to_tz(p_source_date IN DATE,
p_source_tz_delta IN NUMBER,
p_destination_tz_delta IN NUMBER)
RETURN DATE;
/*
* get_top_wait_instances - return the wait data from instances of the named
* oracle_database_group with the largest variance
* on user_wait_pct
*
* p_group_name - the name of the oracle_database_group
* p_top_count - the number of instances' worth of data to return
* p_full_interval - the full display interval (in days)
* p_plot_interval - the desired interval between datapoints (in minutes)
* p_wait_breakdown - a cursor with the data (ordered by target, time)
* returning:
* target_name,
* collection_timestamp,
* user_wait_time,
* user_cpu_time
* p_timezone_delta - the client's locale, expressed in minutes off GMT
* p_end_time - the end time, in the repository's time zone
*/
PROCEDURE get_top_wait_instances(p_group_name IN VARCHAR2,
p_top_count IN NUMBER,
p_full_interval IN NUMBER,
p_plot_interval IN NUMBER,
p_wait_breakdown OUT cursorType,
p_timezone_delta IN NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60,
p_end_time IN DATE DEFAULT SYSDATE);
/*
* get_metric_state_at - Get the state of all metrics of a particular target
* at a point in time.
*
* p_target_guid - The target's identifier
* p_point_in_time - The instant at which the state summary is needed
*
* Returns - cursor containing
*
* mgmt_severity.metric_guid,
* mgmt_severity.key_value,
* mgmt_severity.severity_code,
* p_point_in_time
*/
FUNCTION get_metric_state_at(p_target_guid IN RAW,
p_point_in_time IN DATE)
RETURN cursorType;
/*
* alert type enumeration for regions of alert summary for
* oracle_database_group homepage
*/
g_state_down CONSTANT VARCHAR2(16) := 'DOWN';
g_state_up CONSTANT VARCHAR2(16) := 'UP';
g_state_blackout CONSTANT VARCHAR2(16) := 'BLACKOUT';
g_state_unblackout CONSTANT VARCHAR2(16) := 'UNBLACKOUT';
g_state_critical CONSTANT VARCHAR2(16) := 'CRITICAL';
g_state_warning CONSTANT VARCHAR2(16) := 'WARNING';
g_state_clear CONSTANT VARCHAR2(16) := 'CLEAR';
/*
* get_top_alert_instances - return the alert profile from instances of the
* named oracle_database_group with the priority
* defined by:
* 1) currently down
* 2) most time down
* 3) most time in critical
* 4) most time in warning
* 5) most occurrences of critical
* 6) most occurrences of warning
*
* p_group_name - the name of the oracle_database_group
* p_top_count - the number of instances' worth of data to return
* p_full_interval - the full display interval (in days)
* p_alert_breakdown - a cursor with the data (ordered by target, time)
* returning:
* target_name,
* collection_timestamp,
* alert_status
* p_timezone_delta - the client's locale, expressed in minutes off GMT
* p_end_time - the end time, in the repository's time zone
*/
PROCEDURE get_top_alert_instances(p_group_name IN VARCHAR2,
p_top_count IN NUMBER,
p_full_interval IN NUMBER,
p_alert_breakdown OUT cursorType,
p_timezone_delta IN NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60,
p_end_time IN DATE DEFAULT SYSDATE);
end emd_database;
/
show errors;