Oracle DBMS_SERVER_ALERT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 INT$DBA_ALERT_HISTORY_DETAIL
DBA_ALERT_HISTORY DBMS_PROPAGATION_INTERNAL INT$DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY_DETAIL DBMS_PRVTAQIP THRESHOLD_TYPE
DBA_OUTSTANDING_ALERTS DBMS_SERVER_ALERT_PRVT THRESHOLD_TYPE_SET
DBA_TABLESPACE_THRESHOLDS DBMS_STREAMS_SM UTL_LMS
DBA_THRESHOLDS DBMS_SVRALRT_LIB V$DBFILE
DBMS_BACKUP_RESTORE DBMS_SYS_ERROR WRI$_ALERT_THRESHOLD
DBMS_GSM_ALERTS INT$DBA_ALERT_HISTORY X$KELRTD
Documented Yes
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to the DATAPUMP_IMP_FULL_DATABASE, DBA, DBSNMP, GSMADMIN_INTERNAL, IMP_FULL_DATABASE, OEM_MONITOR and SYSRAC 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
What's New In 12cR1
What's New In 12cR2