Oracle DBMS_SERVER_ALERT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Enables configururation of database server alerts when a threshold for a specified server metric has been violated. Warning and critical thresholds for a large number of predefined metrics are available through this API. If a warning threshold is reached, the server generates a severity level 5 alert. If a critical threshold is reached, the server generates a severity level 1 alert.
Note: Metric Names Constants Names ending with SEC refer are "per second" ... Names ending in TNX are "per transaction"
Note: Value Expressions For the parameters warning_value and critical_value integers between 0 and 100 are treated as percentages: Values larger than 100 are treated as bytes
Note: Warnings Enabled by Default col metrics_name format a25
col warning_operator format a16
col warning_value format a28
col critical_operator format a17
col critical_value format a25

SELECT metrics_name, warning_operator, warning_value, critical_operator, critical_value
FROM dba_thresholds;
AUTHID CURRENT_USER
Constants
Name Data Type Value
Alert Reasons
RSN_SLTE (stateless test alert) REASON_ID_T 0
RSN_SFTE (stateful test alert) REASON_ID_T 1
RSN_SYS_BFCHP (buffer cache hit ratio) REASON_ID_T 2
RSN_FIL_AFRT (avg file read time) REASON_ID_T 3
RSN_SVC_ELAPC (service elapsed time) REASON_ID_T 4
RSN_EVC_AUWC (wait session count) REASON_ID_T 5
RSN_SES_BLUSC (blocked users) REASON_ID_T 6
RSN_SYS_GBKCR (global cache blocks corrupt) REASON_ID_T 7
RSN_SYS_GBKLS (global cache blocks lost) REASON_ID_T 8
RSN_SFTS (tablespace alert) REASON_ID_T 9
RSN_LQWT (long query warning on undo tbs) REASON_ID_T 10
RSN_LQWR (long query warn on rollback seg) REASON_ID_T 11
RSN_OSAT (operation suspended on tablespace) REASON_ID_T 12
RSN_OSAR (oper suspended on rollback seg) REASON_ID_T 13
RSN_OSAD (operation suspended on data) REASON_ID_T 14
RSN_OSAQ (operation suspended on quota) REASON_ID_T 15
RSN_SYS_MSRTP (memory sorts ratio) REASON_ID_T 16
RSN_SYS_RDAHP (redo allocation hit ratio) REASON_ID_T 17
RSN_SYS_UTXNR (user transaction per sec) REASON_ID_T 18
RSN_SYS_PHRDR (physical reads per sec) REASON_ID_T 19
RSN_SYS_PHRDX (physical reads per txn) REASON_ID_T 20
RSN_SYS_PHWRR (physical writes per sec) REASON_ID_T 21
RSN_SYS_PHWRX (physical write per txn) REASON_ID_T 22
RSN_SYS_PRDDR (physical reads direct per sec) REASON_ID_T 23
RSN_SYS_PRDDX (physical reads direct per txn) REASON_ID_T 24
RSN_SYS_PWRDR (physical writes direct per sec) REASON_ID_T 25
RSN_SYS_PWRDX (physcial writes direct per txn) REASON_ID_T 26
RSN_SYS_PRDLR (phys reads direct lobs per sec) REASON_ID_T 27
RSN_SYS_PRDLX (phys reads direct lobs per txn) REASON_ID_T 28
RSN_SYS_PWDLR (phys writes direct lobs per sec) REASON_ID_T 29
RSN_SYS_PWDLX (phys writes direct lobs per txn) REASON_ID_T 30
RSN_SYS_RDGNR (redo generated per sec) REASON_ID_T 31
RSN_SYS_LGNTR (logons per sec) REASON_ID_T 32
RSN_SYS_LGNTX (logons per txn) REASON_ID_T 33
RSN_SYS_OCSTR (open cursors per sec) REASON_ID_T 34
RSN_SYS_OCSTX (open cursors per txn) REASON_ID_T 35
RSN_SYS_UCMTR (user commits per sec) REASON_ID_T 36
RSN_SYS_UCMTP (user commits percentage) REASON_ID_T 37
RSN_SYS_URBKR (user rollbacks per sec) REASON_ID_T 38
RSN_SYS_URBKP (user rollbacks percentage) REASON_ID_T 39
RSN_SYS_UCALR (user calls per sec) REASON_ID_T 40
RSN_SYS_UCALX (user calls per txn) REASON_ID_T 41
RSN_SYS_RCALR (recursive calls per sec) REASON_ID_T 42
RSN_SYS_RCALX (recursive calls per txn) REASON_ID_T 43
RSN_SYS_SLRDR (logical reads per sec) REASON_ID_T 44
RSN_SYS_SLRDX (logical reads per txn) REASON_ID_T 45
RSN_SYS_DWCPR (DBWR checkpoints per sec) REASON_ID_T 46
RSN_SYS_BGCPR (background checkpoints per sec) REASON_ID_T 47
RSN_SYS_RDWRR (redo writes per sec) REASON_ID_T 48
RSN_SYS_RDWRX (redo writes per txn) REASON_ID_T 49
RSN_SYS_LTSCR (long table scans per sec) REASON_ID_T 50
RSN_SYS_LTSCX (long table scans per txn) REASON_ID_T 51
RSN_SYS_TTSCR (total table scans per sec) REASON_ID_T 52
RSN_SYS_TTSCX (total table scans per txn) REASON_ID_T 53
RSN_SYS_FISCR (full index scans per sec) REASON_ID_T 54
RSN_SYS_FISCX (full index scans per txn) REASON_ID_T 55
RSN_SYS_TISCR (total index scans per sec) REASON_ID_T 56
RSN_SYS_TISCX (total index scans per txn) REASON_ID_T 57
RSN_SYS_TPRSR (total parse count per sec) REASON_ID_T 58
RSN_SYS_TPRSX (total parse count per txn) REASON_ID_T 59
RSN_SYS_HPRSR (hard parse count per sec) REASON_ID_T 60
RSN_SYS_HPRSX (hard parse count per txn) REASON_ID_T 61
RSN_SYS_FPRSR (parse failure count per sec) REASON_ID_T 62
RSN_SYS_FPRSX (parse failure count per txn) REASON_ID_T 63
RSN_SYS_CCHTR (cursor cache hit ratio) REASON_ID_T 64
RSN_SYS_DSRTR (disk sort per sec) REASON_ID_T 65
RSN_SYS_DSRTX (disk sort per txn) REASON_ID_T 66
RSN_SYS_RWPST (rows per sort) REASON_ID_T 67
RSN_SYS_XNPRS (execute without parse ratio) REASON_ID_T 68
RSN_SYS_SFPRP (soft parse ratio) REASON_ID_T 69
RSN_SYS_UCALP (user calls ratio) REASON_ID_T 70
RSN_SYS_NTWBR (network traffic volume per sec) REASON_ID_T 71
RSN_SYS_EQTOR (enqueue timeouts per sec) REASON_ID_T 72
RSN_SYS_EQTOX (enqueue timeouts per txn) REASON_ID_T 73
RSN_SYS_EQWTR (enqueue waits per sec) REASON_ID_T 74
RSN_SYS_EQWTX (enqueue waits per txn) REASON_ID_T 75
RSN_SYS_EQDLR (enqueue deadlocks per sec) REASON_ID_T 76
RSN_SYS_EQDLX (enqueue deadlocks per txn) REASON_ID_T 77
RSN_SYS_EQRQR (enqueue requests per sec) REASON_ID_T 78
RSN_SYS_EQRQX (enqueue requests per txn) REASON_ID_T 79
RSN_SYS_DBBGR (db block gets per sec) REASON_ID_T 80
RSN_SYS_DBBGX (db block gets per txn) REASON_ID_T 81
RSN_SYS_CRGTR (consistent read gets per sec) REASON_ID_T 82
RSN_SYS_CRGTX (consistent read gets per txn) REASON_ID_T 83
RSN_SYS_DBBCR (db block changes per sec) REASON_ID_T 84
RSN_SYS_DBBCX (db block changes per txn) REASON_ID_T 85
RSN_SYS_CRCHR (consistent read changes per sec) REASON_ID_T 86
RSN_SYS_CRCHX (consistent read changes per txn) REASON_ID_T 87
RSN_SYS_CPUUR (cpu usage per sec) REASON_ID_T 88
RSN_SYS_CPUUX (cpu usage per txn) REASON_ID_T 89
RSN_SYS_CRBCR (cr blocks created per sec) REASON_ID_T 90
RSN_SYS_CRBCX (cr blocks created per txn) REASON_ID_T 91
RSN_SYS_CRRAX (cr undo records applied per txn) REASON_ID_T 92
RSN_SYS_RBRAR (user rollbk undorec appl per sec) REASON_ID_T 93
RSN_SYS_RBRAX (user rollbk undorec appl per txn) REASON_ID_T 94
RSN_SYS_LNSPR (leaf node splits per sec) REASON_ID_T 95
RSN_SYS_LNSPX (leaf node splits per txn) REASON_ID_T 96
RSN_SYS_BNSPR (branch node splits per sec) REASON_ID_T 97
RSN_SYS_BNSPX (branch node splits per txn) REASON_ID_T 98
RSN_SYS_PX25R (px downgraded 25% or more per sec) REASON_ID_T 99
RSN_SYS_PX50R (px downgraded 50% or more per sec) REASON_ID_T 100
RSN_SYS_PX75R (px downgraded 75% or more per sec) REASON_ID_T 101
RSN_SYS_PXDGR (px downgraded per sec) REASON_ID_T 102
RSN_SYS_PXSRR (px downgraded to serial per sec) REASON_ID_T 103
RSN_SYS_GACRT (global cache average CR get time) REASON_ID_T 104
RSN_SYS_GACUT (global cache ave current get time) REASON_ID_T 105
RSN_SYS_LGONC (current logons count) REASON_ID_T 106
RSN_SYS_OPCSC (current open cursors count) REASON_ID_T 107
RSN_SYS_USLMP (user limit %) REASON_ID_T 108
RSN_SYS_SQSRT (sql service response time) REASON_ID_T 109
RSN_SYS_DBWTT (database wait time ratio) REASON_ID_T 110
RSN_SYS_DBCPT (database cpu time ratio) REASON_ID_T 111
RSN_SYS_RSPTX (response time per txn) REASON_ID_T 112
RSN_SYS_RCHTR (row cache hit ratio) REASON_ID_T 113
RSN_SYS_LCHTR (library cache hit ratio) REASON_ID_T 114
RSN_SYS_LCMSR (library cache miss ratio) REASON_ID_T 115
RSN_SYS_SPFRP (shared pool free %) REASON_ID_T 116
RSN_SYS_PGCHR (pga cache hit %) REASON_ID_T 117
RSN_SYS_PRCLP (process limit %) REASON_ID_T 118
RSN_SYS_SESLP (session limit %) REASON_ID_T 119
RSN_FIL_AFWT (avg file write time) REASON_ID_T 120
RSN_EVC_DTSW (total time waited) REASON_ID_T 121
RSN_SYS_RCMSR (row cache miss ratio) REASON_ID_T 122
RSN_RADL (recovery area disk limit alerts) REASON_ID_T 123
RSN_SYS_RDGNX (redo generated per txn) REASON_ID_T 124
RSN_SYS_CRRAR (cr undo records applied per sec) REASON_ID_T 125
RSN_SYS_THNTF (threshold notice on system type) REASON_ID_T 126
RSN_FIL_THNTF (threshold notice on file type) REASON_ID_T 127
RSN_EVC_THNTF (threshold notice on event class) REASON_ID_T 128
RSN_SVC_THNTF (threshold notice on service) REASON_ID_T 129
RSN_TBS_THNTF (threshold notice on tablespace) REASON_ID_T 130
RSN_SVC_CPUPC (cpu time per user call) REASON_ID_T 131
RSN_SES_THNTF (threshold notice on sessions) REASON_ID_T 132
RSN_SFBTS (tablespace bytes based thresholds) REASON_ID_T 133
RSN_SYS_INQPR (instance should be quiesced) REASON_ID_T 134
RSN_FAN_INSTANCE_UP (instance up) REASON_ID_T 135
RSN_FAN_INSTANCE_DOWN (instance down) REASON_ID_T 136
RSN_FAN_SERVICE_UP (service up REASON_ID_T 137
RSN_FAN_SERVICE_DOWN (service down REASON_ID_T 138
RSN_FAN_SERVICE_MEMBER_UP (svc member up REASON_ID_T 139
RSN_FAN_SERVICE_MEMBER_DOWN (svc member down REASON_ID_T 140
RSN_FAN_SVC_PRECONNECT_UP (preconnect up REASON_ID_T 141
RSN_FAN_SVC_PRECONNECT_DOWN (preconnect down) REASON_ID_T 142
RSN_FAN_NODE_DOWN (node down) REASON_ID_T 143
RSN_FAN_ASM_INSTANCE_UP (asm instance up) REASON_ID_T 144
RSN_FAN_ASM_INSTANCE_DOWN (asm instance down) REASON_ID_T 145
RSN_FAN_DATABASE_UP (database up) REASON_ID_T 146
RSN_FAN_DATABASE_DOWN (database down) REASON_ID_T 147
RSN_SYS_DBTMR (database time per sec) REASON_ID_T 148
RSN_SYS_XCNTR (executions per sec) REASON_ID_T 149
RSN_STR_CAPTURE_ABORTED (capture aborted) REASON_ID_T 150
RSN_STR_APPLY_ABORTED (apply aborted) REASON_ID_T 151
RSN_STR_PROPAGATION_ABORTED (propagation aborted) REASON_ID_T 152
RSN_STR_STREAMSPOOL_FREE_PCT (streams pool free) REASON_ID_T 153
RSN_STR_ERROR_QUEUE (new entry in error queue) REASON_ID_T 154
RSN_LOG_ARCHIVE_LOG_GAP (archived log gap for logminer) REASON_ID_T 155
RSN_SYS_ACTVS (average active sessions) REASON_ID_T 156
RSN_SYS_SRLAT (average synchronous single-block read latency) REASON_ID_T 157
RSN_SYS_IOMBS (i/o megabytes) REASON_ID_T 158
RSN_SYS_IOREQ (i/o requests) REASON_ID_T 159
RSN_WCR_IOLAT (average IO latency) REASON_ID_T 160
RSN_WCR_PCPU (% of replay threads on CPU) REASON_ID_T 161
RSN_WCR_PIO (% of replay threads doing IO) REASON_ID_T 162
RSN_WRC_THNTF (threshold notice on WRCLIENT type) REASON_ID_T 163
RSN_WRC_STATUS (change of status for capture/replay) REASON_ID_T 164
RSN_STR_SPLIT_MERGE (auto split/merge) REASON_ID_T 166
RSN_XSTR_CAPTURE_ABORTED (capture aborted) REASON_ID_T 167
RSN_XSTR_APPLY_ABORTED (apply aborted) REASON_ID_T 168
RSN_XSTR_PROPAGATION_ABORTED (propagation aborted) REASON_ID_T 169
RSN_XSTR_ERROR_QUEUE (new entry in error queue) REASON_ID_T 170
RSN_XSTR_SPLIT_MERGE (auto split/merge) REASON_ID_T 171
RSN_GG_CAPTURE_ABORTED (capture aborted) REASON_ID_T 172
RSN_GG_APPLY_ABORTED (apply aborted) REASON_ID_T 173
RSN_GG_PROPAGATION_ABORTED (propagation aborted) REASON_ID_T 174
RSN_GG_ERROR_QUEUE (new entry in error queue) REASON_ID_T 175
RSN_GG_SPLIT_MERGE (auto split/merge) REASON_ID_T 176
Message Levels
LEVEL_CRITICAL PLS_INTEGER 1
LEVEL_WARNING PLS_INTEGER 5
LEVEL_CLEAR PLS_INTEGER 32
Metrics Names
AVG_USERS_WAITING BINARY_INTEGER 1000
DB_TIME_WAITING BINARY_INTEGER 1001
BUFFER_CACHE_HIT BINARY_INTEGER 2000
MEMORY_SORTS_PCT BINARY_INTEGER 2001
REDO_ALLOCATION_HIT BINARY_INTEGER 2002
USER_TRANSACTIONS_SEC BINARY_INTEGER 2003
PHYSICAL_READS_SEC BINARY_INTEGER 2004
PHYSICAL_READS_TXN BINARY_INTEGER 2005
PHYSICAL_WRITES_SEC BINARY_INTEGER 2006
PHYSICAL_WRITES_TXN BINARY_INTEGER 2007
PHYSICAL_READS_DIR_SEC BINARY_INTEGER 2008
PHYSICAL_READS_DIR_TXN BINARY_INTEGER 2009
PHYSICAL_WRITES_DIR_SEC BINARY_INTEGER 2010
PHYSICAL_WRITES_DIR_TXN BINARY_INTEGER 2011
PHYSICAL_READS_LOB_SEC BINARY_INTEGER 2012
PHYSICAL_READS_LOB_TXN BINARY_INTEGER 2013
PHYSICAL_WRITES_LOB_SEC BINARY_INTEGER 2014
PHYSICAL_WRITES_LOB_TXN BINARY_INTEGER 2015
REDO_GENERATED_SEC BINARY_INTEGER 2016
REDO_GENERATED_TXN BINARY_INTEGER 2017
LOGONS_SEC BINARY_INTEGER 2018
LOGONS_TXN BINARY_INTEGER 2019
OPEN_CURSORS_SEC BINARY_INTEGER 2020
OPEN_CURSORS_TXN BINARY_INTEGER 2021
USER_COMMITS_SEC BINARY_INTEGER 2022
USER_COMMITS_TXN BINARY_INTEGER 2023
USER_ROLLBACKS_SEC BINARY_INTEGER 2024
USER_ROLLBACKS_TXN BINARY_INTEGER 2025
USER_CALLS_SEC BINARY_INTEGER 2026
USER_CALLS_TXN BINARY_INTEGER 2027
RECURSIVE_CALLS_SEC BINARY_INTEGER 2028
RECURSIVE_CALLS_TXN BINARY_INTEGER 2029
SESS_LOGICAL_READS_SEC BINARY_INTEGER 2030
SESS_LOGICAL_READS_TXN BINARY_INTEGER 2031
DBWR_CKPT_SEC BINARY_INTEGER 2032
BACKGROUND_CKPT_SEC BINARY_INTEGER 2033
REDO_WRITES_SEC BINARY_INTEGER 2034
REDO_WRITES_TXN BINARY_INTEGER 2035
LONG_TABLE_SCANS_SEC BINARY_INTEGER 2036
LONG_TABLE_SCANS_TXN BINARY_INTEGER 2037
TOTAL_TABLE_SCANS_SEC BINARY_INTEGER 2038
TOTAL_TABLE_SCANS_TXN BINARY_INTEGER 2039
FULL_INDEX_SCANS_SEC BINARY_INTEGER 2040
FULL_INDEX_SCANS_TXN BINARY_INTEGER 2041
TOTAL_INDEX_SCANS_SEC BINARY_INTEGER 2042
TOTAL_INDEX_SCANS_TXN BINARY_INTEGER 2043
TOTAL_PARSES_SEC BINARY_INTEGER 2044
TOTAL_PARSES_TXN BINARY_INTEGER 2045
HARD_PARSES_SEC BINARY_INTEGER 2046
HARD_PARSES_TXN BINARY_INTEGER 2047
PARSE_FAILURES_SEC BINARY_INTEGER 2048
PARSE_FAILURES_TXN BINARY_INTEGER 2049
CURSOR_CACHE_HIT BINARY_INTEGER 2050
DISK_SORT_SEC BINARY_INTEGER 2051
DISK_SORT_TXN BINARY_INTEGER 2052
ROWS_PER_SORT BINARY_INTEGER 2053
EXECUTE_WITHOUT_PARSE BINARY_INTEGER 2054
SOFT_PARSE_PCT BINARY_INTEGER 2055
USER_CALLS_PCT BINARY_INTEGER 2056
NETWORK_BYTES_SEC BINARY_INTEGER 2058
ENQUEUE_TIMEOUTS_SEC BINARY_INTEGER 2059
ENQUEUE_TIMEOUTS_TXN BINARY_INTEGER 2060
ENQUEUE_WAITS_SEC BINARY_INTEGER 2061
ENQUEUE_WAITS_TXN BINARY_INTEGER 2062
ENQUEUE_DEADLOCKS_SEC BINARY_INTEGER 2063
ENQUEUE_DEADLOCKS_TXN BINARY_INTEGER 2064
ENQUEUE_REQUESTS_SEC BINARY_INTEGER 2065
ENQUEUE_REQUESTS_TXN BINARY_INTEGER 2066
DB_BLKGETS_SEC BINARY_INTEGER 2067
DB_BLKGETS_TXN BINARY_INTEGER 2068
CONSISTENT_GETS_SEC BINARY_INTEGER 2069
CONSISTENT_GETS_TXN BINARY_INTEGER 2070
DB_BLKCHANGES_SEC BINARY_INTEGER 2071
DB_BLKCHANGES_TXN BINARY_INTEGER 2072
CONSISTENT_CHANGES_SEC BINARY_INTEGER 2073
CONSISTENT_CHANGES_TXN BINARY_INTEGER 2074
SESSION_CPU_SEC BINARY_INTEGER 2075
SESSION_CPU_TXN BINARY_INTEGER 2076
CR_BLOCKS_CREATED_SEC BINARY_INTEGER 2077
CR_BLOCKS_CREATED_TXN BINARY_INTEGER 2078
CR_RECORDS_APPLIED_SEC BINARY_INTEGER 2079
CR_RECORDS_APPLIED_TXN BINARY_INTEGER 2080
RB_RECORDS_APPLIED_SEC BINARY_INTEGER 2081
RB_RECORDS_APPLIED_TXN BINARY_INTEGER 2082
LEAF_NODE_SPLITS_SEC BINARY_INTEGER 2083
LEAF_NODE_SPLITS_TXN BINARY_INTEGER 2084
BRANCH_NODE_SPLITS_SEC BINARY_INTEGER 2085
BRANCH_NODE_SPLITS_TXN BINARY_INTEGER 2086
PX_DOWNGRADED_25_SEC BINARY_INTEGER 2087
PX_DOWNGRADED_50_SEC BINARY_INTEGER 2088
PX_DOWNGRADED_75_SEC BINARY_INTEGER 2089
PX_DOWNGRADED_SEC BINARY_INTEGER 2090
PX_DOWNGRADED_SER_SEC BINARY_INTEGER 2091
GC_AVG_CR_GET_TIME BINARY_INTEGER 2098
GC_AVG_CUR_GET_TIME BINARY_INTEGER 2099
GC_BLOCKS_CORRUPT BINARY_INTEGER 2101
GC_BLOCKS_LOST BINARY_INTEGER 2102
LOGONS_CURRENT BINARY_INTEGER 2103
OPEN_CURSORS_CURRENT BINARY_INTEGER 2104
USER_LIMIT_PCT BINARY_INTEGER 2105
SQL_SRV_RESPONSE_TIME BINARY_INTEGER 2106
DATABASE_WAIT_TIME BINARY_INTEGER 2107
DATABASE_CPU_TIME BINARY_INTEGER 2108
RESPONSE_TXN BINARY_INTEGER 2109
ROW_CACHE_HIT BINARY_INTEGER 2110
ROW_CACHE_MISS BINARY_INTEGER 2111
LIBARY_CACHE_HIT BINARY_INTEGER 2112
LIBARY_CACHE_MISS BINARY_INTEGER 2113
SHARED_POOL_FREE_PCT BINARY_INTEGER 2114
PGA_CACHE_HIT BINARY_INTEGER 2115
PROCESS_LIMIT_PCT BINARY_INTEGER 2118
SESSION_LIMIT_PCT BINARY_INTEGER 2119
EXECUTIONS_PER_SEC BINARY_INTEGER 2121
DB_TIME_PER_SEC BINARY_INTEGER 2123
STREAMS_POOL_USED_PCT BINARY_INTEGER 2136
BLOCKED_USERS BINARY_INTEGER 4000
ELAPSED_TIME_PER_CALL BINARY_INTEGER 6000
CPU_TIME_PER_CALL BINARY_INTEGER 6001
AVG_FILE_READ_TIME BINARY_INTEGER 7000
AVG_FILE_WRITE_TIME BINARY_INTEGER 7001
TABLESPACE_PCT_FULL BINARY_INTEGER 9000
TABLESPACE_BYT_FREE BINARY_INTEGER 9001
WCR_AVG_IO_LAT BINARY_INTEGER 13000
WCR_PCPU BINARY_INTEGER 13001
WCR_PIO BINARY_INTEGER 13002
Object Types
OBJECT_TYPE_SYSTEM BINARY_INTEGER 1
OBJECT_TYPE_FILE BINARY_INTEGER 2
OBJECT_TYPE_SERVICE BINARY_INTEGER 3
OBJECT_TYPE_EVENT_CLASS BINARY_INTEGER 4
OBJECT_TYPE_TABLESPACE BINARY_INTEGER 5
OBJECT_TYPE_SESSION BINARY_INTEGER 9
OBJECT_TYPE_WRCLIENT BINARY_INTEGER 16
Operator Types
OPERATOR_GT BINARY_INTEGER 0
OPERATOR_EQ BINARY_INTEGER 1
OPERATOR_LT BINARY_INTEGER 2
OPERATOR_LE BINARY_INTEGER 3
OPERATOR_GE BINARY_INTEGER 4
OPERATOR_CONTAINS BINARY_INTEGER 5
OPERATOR_NE BINARY_INTEGER 6
OPERATOR_DO_NOT_CHECK BINARY_INTEGER 7
Data Types and Subtypes SUBTYPE REASON_ID_T IS PLS_INTEGER;

SUBTYPE SEVERITY_LEVEL_T IS PLS_INTEGER;

CREATE TYPE threshold_type AS OBJECT(
object_type             NUMBER,
object_name             VARCHAR2(513),
metrics_id              NUMBER,
instance_name           VARCHAR2(16),
flags                   NUMBER,
warning_operator        NUMBER,
warning_value           VARCHAR2(256),
critical_operator       NUMBER,
critical_value          VARCHAR2(256),
observation_period      NUMBER,
consecutive_occurrences NUMBER,
object_id               NUMBER);

-- create threshold set type for threshold table function
CREATE TYPE threshold_type_set AS TABLE OF threshold_type;

-- create alert type used for in AQ messages
CREATE TYPE sys.alert_type AS OBJECT (
timestamp_originating   TIMESTAMP WITH TIME ZONE,
organization_id         VARCHAR2(10),
component_id            VARCHAR2(3),
message_id              NUMBER,
hosting_client_id       VARCHAR2(64),
message_type            VARCHAR2(12),
message_group           VARCHAR2(30),
message_level           NUMBER,
host_id                 VARCHAR2(256),
host_nw_addr            VARCHAR2(256),
module_id               VARCHAR2(50),
process_id              VARCHAR2(128),
user_id                 VARCHAR2(30),
upstream_component_id   VARCHAR2(30),
downstream_component_id VARCHAR2(4),
execution_context_id    VARCHAR2(128),
error_instance_id       VARCHAR2(142),
reason_argument_count   NUMBER,
reason_argument_1       VARCHAR2(513),
reason_argument_2       VARCHAR2(513),
reason_argument_3       VARCHAR2(513),
reason_argument_4       VARCHAR2(513),
reason_argument_5       VARCHAR2(513),
sequence_id             NUMBER,
reason_id               NUMBER,
object_owner            VARCHAR2(30),
object_name             VARCHAR2(513),
subobject_name          VARCHAR2(30),
object_type             VARCHAR2(30),
instance_name           VARCHAR2(16),
instance_number         NUMBER,
scope                   VARCHAR2(10),
advisor_name            VARCHAR2(30),
metric_value            NUMBER,
suggested_action_msg_id NUMBER,
action_argument_count   NUMBER,
action_argument_1       VARCHAR2(30),
action_argument_2       VARCHAR2(30),
action_argument_3       VARCHAR2(30),
action_argument_4       VARCHAR2(30),
action_argument_5       VARCHAR2(30)
pdb_name                VARCHAR2(128));
Default Database Threshold -- the following code is run by the catalrt.sql script during database installation
BEGIN
  dbms_server_alert.set_threshold(9000, NULL, NULL, NULL, NULL, 1, 1, '', 5, '');
EXCEPTION
  WHEN OTHERS THEN
    IF sqlcode = -00001 THEN
      NULL; -- unique constraint error
    ELSE
      RAISE;
    END IF;
END;
/

-- to set the default database thresholds
Dependencies
BSLN_INTERNAL DBMS_HA_ALERTS_PRVT THRESHOLD_TYPE
DBA_ALERT_HISTORY DBMS_PROPAGATION_INTERNAL THRESHOLD_TYPE_SET
DBA_ALERT_HISTORY_DETAIL DBMS_PRVTAQIP UTL_LMS
DBA_OUTSTANDING_ALERTS DBMS_SERVER_ALERT_PRVT V$DBFILE
DBA_TABLESPACE_THRESHOLDS DBMS_STREAMS_SM WRI$_ALERT_THRESHOLD
DBA_THRESHOLDS DBMS_SVRALRT_LIB X$KELRTD
DBMS_BACKUP_RESTORE DBMS_SYS_ERROR  
Documented Yes
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to the DBA and IMP_FULL_DATABASE roles.
Source {ORACLE_HOME}/rdbms/admin/catalrt.sql
{ORACLE_HOME}/rdbms/admin/dbmsslrt.sql
Subprograms
 
EXPAND_MESSAGE (new 12.1 parameters)
Expand Alert Message dbms_server_alert.expand_message(
user_language IN VARCHAR2,
message_id    IN NUMBER,
argument_1    IN VARCHAR2,
argument_2    IN VARCHAR2,
argument_3    IN VARCHAR2,
argument_4    IN VARCHAR2,
argument_5    IN VARCHAR2,
objargpos     IN NUMBER   DEFAULT -1,
pdb_name      IN VARCHAR2 DEFAULT NULL))
RETURN VARCHAR2;
-- create an queue agent
exec dbms_aqadm.create_aq_agent(agent_name => 'ALERT_AGT');

-- subscribe to alert_que
exec dbms_aqadm.add_subscriber(queue_name => 'ALERT_QUE', subscriber => AQ$_AGENT('ALERT_AGT','',0));

-- associate user with the secure queue
exec dbms_aqadm.enable_db_access(agent_name => 'ALERT_AGT', db_username=>'SYSTEM');

exec dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'ALERT_QUE',grantee => 'SYSTEM', grant_option => FALSE);

-- dequeue an alert
DECLARE
 dequeue_options    dbms_aq.dequeue_options_t;
 message_properties dbms_aq.message_properties_t;
 message            ALERT_TYPE;
 message_handle     RAW(16);
BEGIN
  dequeue_options.consumer_name := 'ALERTAGENT';
  dequeue_options.wait := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;

  dbms_aq.dequee(queue_name => 'ALERT_QUE', dequeue_options =>
  dequeue_options, message_properties => message_properties,
  payload => message, msgid => message_handle);

  dbms_output.put_line('Alert message dequeued:');
  dbms_output.put_line(' Timestamp: ' || message.timestamp_originating);
  dbms_output.put_line('Organization Id: ' || message.organization_id);
  dbms_output.put_line('Component Id: ' || message.component_id);
  dbms_output.put_line('Message Type: ' || message.message_type);
  dbms_output.put_line('Message Group: ' || message.message_group);
  dbms_output.put_line('Message Level: ' || message.message_level);
  dbms_output.put_line('Host Id: ' || message.host_id);
  dbms_output.put_line('Host Network Addr: ' ||  message.host_nw_addr);

  dbms_output.put_line(' Reason: ' ||
  dbms_server_alert.expand_message(userenv('LANGUAGE'),
  message.message_id, message.reason_argument_1,
  message.reason_argument_2, message.reason_argument_3,
  message.reason_argument_4, message.reason_argument_5)
);

  dbms_output.put_line('Sequence Id:   ' || message.sequence_id);
  dbms_output.put_line('Reason Id:     ' || message.reason_id);
  dbms_output.put_line('Object Name:   ' || message.object_name);
  dbms_output.put_line('Object Type:   ' || message.object_type);
  dbms_output.put_line('Instance Name: ' || message.instance_name);

  dbms_output.put_line('Suggested action: ' ||
  dbms_server_alert.expand_message(userenv('LANGUAGE')
,
  message.suggested_action_msg_id
, message.action_argument_1,
  message.action_argument_2
, message.action_argument_3,
  message.action_argument_4
, message.action_argument_5));

  dbms_output.put_line('Advisor Name: ' || message.advisor_name);
  dbms_output.put_line(' Scope: ' || message.scope);
END;
/
 
GET_THRESHOLD
Gets the threshold setting for a given metric dbms_server_alert.get_threshold(
metrics_id              IN  BINARY_INTEGER,
warning_operator        OUT BINARY_INTEGER,
warning_value           OUT VARCHAR2,
critical_operator       OUT BINARY_INTEGER,
critical_value          OUT VARCHAR2,
observation_period      OUT BINARY_INTEGER, -- default 10 min.
consecutive_occurrences OUT BINARY_INTEGER,
instance_name           IN  VARCHAR2,       -- NULL for db-wide alerts
object_type             IN  BINARY_INTEGER,
object_name             IN  VARCHAR2);
set serveroutput on

DECLARE
 vWarnOp  NUMBER(10);
 vWarnVal VARCHAR2(100);
 vCritOp  NUMBER(10);
 vCritVal VARCHAR2(100);
 vObsvPer NUMBER(5);
 vConOcur NUMBER(5);
BEGIN
  dbms_server_alert.get_threshold(dbms_server_alert.tablespace_pct_full, vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur, NULL, dbms_server_alert.object_type_tablespace, 'UWDATA');

  dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
  dbms_output.put_line('WarnVal: ' || vWarnVal);
  dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
  dbms_output.put_line('CritVal: ' || vCritVal);
END;
/
 
SET_THRESHOLD
Sets the threshold setting for a given metric dbms_server_alert.set_threshold(
metrics_id              IN BINARY_INTEGER,
warning_operator        IN BINARY_INTEGER,
warning_value           IN VARCHAR2,
critical_operator       IN BINARY_INTEGER,
critical_value          IN VARCHAR2,
observation_period      IN BINARY_INTEGER,   -- default 10 min.
consecutive_occurrences IN BINARY_INTEGER,
instance_name           IN VARCHAR2,         -- NULL for db-wide alerts
object_type             IN BINARY_INTEGER,
object_name             IN VARCHAR2);
-- warning_operator = tablespace_pct_full
-- warning = 80
-- critical = 95
-- observation_period = 1
-- consecutive occurrences = 1
-- instance_name is NULL
-- object_type = tablespace
-- object_name = UWDATA

-- start space usage checking

BEGIN
  dbms_server_alert.set_threshold(dbms_server_alert.tablespace_pct_full, dbms_server_alert.operator_ge, 80,
  dbms_server_alert.operator_ge, 95, 1, 1, NULL, dbms_server_alert.object_type_tablespace, 'UWDATA');
END;
/

col warning_value format a20
col critical_value format a20

SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA';

-- check status
SELECT reason, resolution
FROM dba_alert_history
WHERE object_name = 'UWDATA';

-- check for alerts
SELECT reason, message_level, DECODE(message_level, 5, 'WARNING', 1, 'CRITICAL') ALERT_LEVEL
FROM dba_outstanding_alerts
WHERE object_name = 'UWDATA';

-- stop space usage checking
BEGIN
  dbms_server_alert.set_threshold(dbms_server_alert.tablespace_pct_full, dbms_server_alert.operator_do_not_check, '0',
    dbms_server_alert.operator_do_not_check, '0', 1, 1, NULL, dbms_server_alert.object_type_tablespace, 'UWDATA');
END;
/

SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA';

-- reset the usage threshold
BEGIN
  dbms_server_alert.set_threshold(dbms_server_alert.tablespace_pct_full, NULL, NULL, NULL, NULL, 1, 1, NULL,
    dbms_server_alert.object_type_tablespace, 'UWDATA');
END;
/

SELECT warning_value, critical_value, status
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Usage'
AND object_name = 'UWDATA';
 
VIEW_THRESHOLDS
Pipelined table function used to  create the dictionary view DBA_THRESHOLDS dbms_server_alert.view_thresholds RETURN threshold_type_set PIPELINED;
set linesize 141
col metrics_id format 9999
col warning_value format a15
col critical_value format a15

SELECT object_type,metrics_id, instance_name, flags, warning_operator, warning_value, critical_operator, critical_value, observation_period, consecutive_occurrences, object_id
FROM TABLE(dbms_server_alert.view_thresholds);
 
Demos
CPU Monitoring -- examine current settings
DECLARE
 vWarnOp  NUMBER(10);
 vWarnVal VARCHAR2(100);
 vCritOp  NUMBER(10);
 vCritVal VARCHAR2(100);
 vObsvPer NUMBER(5);
 vConOcur NUMBER(5);
BEGIN
  dbms_server_alert.get_threshold(dbms_server_alert.session_cpu_sec,
  vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur, NULL,
  dbms_server_alert.OBJECT_TYPE_SYSTEM, NULL);

  dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
  dbms_output.put_line('WarnVal: ' || vWarnVal);
  dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
  dbms_output.put_line('CritVal: ' || vCritVal);

  dbms_server_alert.get_threshold(dbms_server_alert.session_cpu_txn,
  vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur, NULL,
  dbms_server_alert.object_type_system, NULL);

  dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
  dbms_output.put_line('WarnVal: ' || vWarnVal);
  dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
  dbms_output.put_line('CritVal: ' || vCritVal);
END;
/

-- set thresholds
BEGIN
  dbms_server_alert.set_threshold(dbms_server_alert.session_cpu_sec,
    dbms_server_alert.operator_ge, 200, dbms_server_alert.operator_ge,
    400, 1, 1, NULL, dbms_server_alert.object_type_system, NULL);

  dbms_server_alert.set_threshold(dbms_server_alert.session_cpu_txn,
    dbms_server_alert.operator_ge, 25, dbms_server_alert.operator_ge,
    40, 1, 1, NULL, dbms_server_alert.object_type_system, NULL);
END;
/

-- drop the alerts
BEGIN
  dbms_server_alert.set_threshold(dbms_server_alert.session_cpu_sec, NULL, NULL,
    NULL, NULL, 1, 1, NULL, dbms_server_alert.object_type_system, NULL);

  dbms_server_alert.set_threshold(dbms_server_alert.session_cpu_txn, NULL, NULL,
    NULL, NULL, 1, 1, NULL, dbms_server_alert.object_type_system, NULL);
END;
/
 
Related Queries
View Alert Types set linesize 121
col object_type format a23
col type format a9
col internal_metric_category format a29
col internal_metric_name format a31

SELECT reason_id, object_type, type, internal_metric_category, internal_metric_name
FROM gv$alert_types
ORDER BY 2,1;

Related Topics
DBMS_ALERT
DBMS_AQ
DBMS_AQADM
DBMS_SERVER_ALERT_EXPORT
DBMS_SERVER_ALERT_PRVT
Packages