Oracle | DBMS_SERVER

Oracle DBMS_SERVER_ALERT Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 DEFINER

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
RSN_GDS_GSM_DOWN (GDS GSM down)	REASON_ID_T	177
RSN_GDS_INSTANCE_DOWN (GDS instance down)	REASON_ID_T	178
RSN_GDS_SVC_CARD_MISSED (GDS svc cardinality missed)	REASON_ID_T	179
RSN_GDS_SVC_THRESH (GDS svc threshold)	REASON_ID_T	180
RSN_GDS_SVC_LAG (GDS svc lagging)	REASON_ID_T	181
RSN_GDS_CATALOG_DOWN (GDS catalog down)	REASON_ID_T	182
RSN_GDS_DATABASE_DOWN (GDS database down)	REASON_ID_T	183
RSN_APPLY_RESTARTED (apply restarted)	REASON_ID_T	184
RSN_XSTR_APPLY_RESTARTED (Xstream apply restarted)	REASON_ID_T	185
RSN_GG_APPLY_RESTARTED (GoldenGate apply restarted)	REASON_ID_T	186
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
FRA_PCT_FULL	BINARY_INTEGER	19000
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(6) 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(64), 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(128), 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_GSM_ALERTS	DIR$ALERT_HISTORY
CDB_ALERT_HISTORY	DBMS_HA_ALERTS_PRVT	THRESHOLD_TYPE
CDB_ALERT_HISTORY_DETAIL	DBMS_PROPAGATION_INTERNAL	THRESHOLD_TYPE_SET
DBA_ALERT_HISTORY	DBMS_PRVTAQIP	UTL_LMS
DBA_ALERT_HISTORY_DETAIL	DBMS_SERVER_ALERT_PRVT	V$DBFILE
DBA_OUTSTANDING_ALERTS	DBMS_STREAMS_SM	WRI$_ALERT_HISTORY
DBA_TABLESPACE_THRESHOLDS	DBMS_SVRALRT_LIB	WRI$_ALERT_THRESHOLD
DBA_THRESHOLDS	DBMS_SYS_ERROR	X$KELRTD
DBMS_BACKUP_RESTORE

Documented Yes

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the DATAPUMP_IMP_FULL_DATABASE, DBA, DBSNMP, GSMADMIN_INTERNAL, IMP_FULL_DATABASE, and OEM_MONITOR roles and SYSRAC.

Source {ORACLE_HOME}/rdbms/admin/dbmsslrt.sql

Subprograms

EXPAND_MESSAGE	SET_THRESHOLD	Demos
GET_THRESHOLD	VIEW_THRESHOLDS	Related Queries

EXPAND_MESSAGE

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 181

      col obj_type format 99

      col metrics_id format 9999

      col warning_value format a30

      col crit_val format a10

      

      SELECT object_type OBJ_TYPE, metrics_id, instance_name, flags, warning_operator, warning_value, critical_operator,
      critical_value CRIT_VAL, observation_period OBS_PER, 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;