Oracle DBMS_SERVER


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SERVER_ALERT

Version 11.1

General Information

Source

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

First Available

10.1

Constants: Message Levels

Name	Data Type	Value
SUBTYPE SEVERITY_LEVEL_T	PLS_INTEGER	NULL
LEVEL_CRITICAL	PLS_INTEGER	1
LEVEL_WARNING	PLS_INTEGER	5
LEVEL_CLEAR	PLS_INTEGER	32

Constants: Alert Reasons

Name	Data Type	Value	Description
RSN_SLTE	REASON_ID_T	0	stateless test alert
RSN_SFTE	REASON_ID_T	1	stateful test alert
RSN_SYS_BFCHP	REASON_ID_T	2	buffer cache hit ratio
RSN_FIL_AFRT	REASON_ID_T	3	avg file read time
RSN_SVC_ELAPC	REASON_ID_T	4	service elapsed time
RSN_EVC_AUWC	REASON_ID_T	5	wait session count
RSN_SES_BLUSC	REASON_ID_T	6	blocked users
RSN_SYS_GBKCR	REASON_ID_T	7	global cache blocks corrupt
RSN_SYS_GBKLS	REASON_ID_T	8	global cache blocks lost
RSN_SFTS	REASON_ID_T	9	tablespace alert
RSN_LQWT	REASON_ID_T	10	long query warning on undo tbs
RSN_LQWR	REASON_ID_T	11	long query warn on rollback seg
RSN_OSAT	REASON_ID_T	12	operation suspended on tablespace
RSN_OSAR	REASON_ID_T	13	oper suspended on rollback seg
RSN_OSAD	REASON_ID_T	14	operation suspended on data
RSN_OSAQ	REASON_ID_T	15	operation suspended on quota
RSN_SYS_MSRTP	REASON_ID_T	16	memory sorts ratio
RSN_SYS_RDAHP	REASON_ID_T	17	redo allocation hit ratio
RSN_SYS_UTXNR	REASON_ID_T	18	user transaction per sec
RSN_SYS_PHRDR	REASON_ID_T	19	physical reads per sec
RSN_SYS_PHRDX	REASON_ID_T	20	physical reads per txn
RSN_SYS_PHWRR	REASON_ID_T	21	physical writes per sec
RSN_SYS_PHWRX	REASON_ID_T	22	physical writes per txn
RSN_SYS_PRDDR	REASON_ID_T	23	physical reads direct per sec
RSN_SYS_PRDDX	REASON_ID_T	24	physical reads direct per txn
RSN_SYS_PWRDR	REASON_ID_T	25	physical writes direct per sec
RSN_SYS_PWRDX	REASON_ID_T	26	physical writes direct per txn
RSN_SYS_PRDLR	REASON_ID_T	27	phys reads direct lobs per sec
RSN_SYS_PRDLX	REASON_ID_T	28	phys reads direct lobs per txn
RSN_SYS_PWDLR	REASON_ID_T	29	phys writes direct lobs per sec
RSN_SYS_PWDLX	REASON_ID_T	30	phys writes direct lobs per txn
RSN_SYS_RDGNR	REASON_ID_T	31	redo generated per sec
RSN_SYS_LGNTR	REASON_ID_T	32	logons per sec
RSN_SYS_LGNTX	REASON_ID_T	33	logons per txn
RSN_SYS_OCSTR	REASON_ID_T	34	open cursors per sec
RSN_SYS_OCSTX	REASON_ID_T	35	open cursors per txn
RSN_SYS_UCMTR	REASON_ID_T	36	user commits per sec
RSN_SYS_UCMTP	REASON_ID_T	37	user commits percentage
RSN_SYS_URBKR	REASON_ID_T	38	user rollbacks per sec
RSN_SYS_URBKP	REASON_ID_T	39	user rollbacks percentage
RSN_SYS_UCALR	REASON_ID_T	40	user calls per sec
RSN_SYS_UCALX	REASON_ID_T	41	user calls per txn
RSN_SYS_RCALR	REASON_ID_T	42	recursive calls per sec
RSN_SYS_RCALX	REASON_ID_T	43	recursive calls per txn
RSN_SYS_SLRDR	REASON_ID_T	44	logical reads per sec
RSN_SYS_SLRDX	REASON_ID_T	45	logical reads per txn
RSN_SYS_DWCPR	REASON_ID_T	46	DBWR checkpoints per sec
RSN_SYS_BGCPR	REASON_ID_T	47	background checkpoints per sec
RSN_SYS_RDWRR	REASON_ID_T	48	redo writes per sec
RSN_SYS_RDWRX	REASON_ID_T	49	redo writes per txn
RSN_SYS_LTSCR	REASON_ID_T	50	long table scans per sec
RSN_SYS_LTSCX	REASON_ID_T	51	long table scans per txn
RSN_SYS_TTSCR	REASON_ID_T	52	total table scans per sec
RSN_SYS_TTSCX	REASON_ID_T	53	total table scans per txn
RSN_SYS_FISCR	REASON_ID_T	54	full index scans per sec
RSN_SYS_FISCX	REASON_ID_T	55	full index scans per txn
RSN_SYS_TISCR	REASON_ID_T	56	total index scans per sec
RSN_SYS_TISCX	REASON_ID_T	57	total index scans per txn
RSN_SYS_TPRSR	REASON_ID_T	58	total parse count per sec
RSN_SYS_TPRSX	REASON_ID_T	59	total parse count per txn
RSN_SYS_HPRSR	REASON_ID_T	60	hard parse count per sec
RSN_SYS_HPRSX	REASON_ID_T	61	hard parse count per txn
RSN_SYS_FPRSR	REASON_ID_T	62	parse failure count per sec
RSN_SYS_FPRSX	REASON_ID_T	63	parse failure count per txn
RSN_SYS_CCHTR	REASON_ID_T	64	cursor cache hit ratio
RSN_SYS_DSRTR	REASON_ID_T	65	disk sort per sec
RSN_SYS_DSRTX	REASON_ID_T	66	disk sort per txn
RSN_SYS_RWPST	REASON_ID_T	67	rows per sort
RSN_SYS_XNPRS	REASON_ID_T	68	execute without parse ratio
RSN_SYS_SFPRP	REASON_ID_T	69	soft parse ratio
RSN_SYS_UCALP	REASON_ID_T	70	user calls ratio
RSN_SYS_NTWBR	REASON_ID_T	71	network traffic volume per sec
RSN_SYS_EQTOR	REASON_ID_T	72	enqueue timeouts per sec
RSN_SYS_EQTOX	REASON_ID_T	73	enqueue timeouts per txn
RSN_SYS_EQWTR	REASON_ID_T	74	enqueue waits per sec
RSN_SYS_EQWTX	REASON_ID_T	75	enqueue waits per txn
RSN_SYS_EQDLR	REASON_ID_T	76	enqueue deadlocks per sec
RSN_SYS_EQDLX	REASON_ID_T	77	enqueue deadlocks per txn
RSN_SYS_EQRQR	REASON_ID_T	78	enqueue requests per sec
RSN_SYS_EQRQX	REASON_ID_T	79	enqueue requests per txn
RSN_SYS_DBBGR	REASON_ID_T	80	db block gets per sec
RSN_SYS_DBBGX	REASON_ID_T	81	db block gets per txn
RSN_SYS_CRGTR	REASON_ID_T	82	consistent read gets per sec
RSN_SYS_CRGTX	REASON_ID_T	83	consistent read gets per txn
RSN_SYS_DBBCR	REASON_ID_T	84	db block changes per sec
RSN_SYS_DBBCX	REASON_ID_T	85	db block changes per txn
RSN_SYS_CRCHR	REASON_ID_T	86	consistent read changes per sec
RSN_SYS_CRCHX	REASON_ID_T	87	consistent read changes per txn
RSN_SYS_CPUUR	REASON_ID_T	88	cpu usage per sec
RSN_SYS_CPUUX	REASON_ID_T	89	cpu usage per txn
RSN_SYS_CRBCR	REASON_ID_T	90	cr blocks created per sec
RSN_SYS_CRBCX	REASON_ID_T	91	cr blocks created per txn
RSN_SYS_CRRAX	REASON_ID_T	92	cr undo records applied per txn
RSN_SYS_RBRAR	REASON_ID_T	93	user rollbk undorec appl per sec
RSN_SYS_RBRAX	REASON_ID_T	94	user rollbk undorec appl per txn
RSN_SYS_LNSPR	REASON_ID_T	95	leaf node splits per sec
RSN_SYS_LNSPX	REASON_ID_T	96	leaf node splits per txn
RSN_SYS_BNSPR	REASON_ID_T	97	branch node splits per sec
RSN_SYS_BNSPX	REASON_ID_T	98	branch node splits per txn
RSN_SYS_PX25R	REASON_ID_T	99	px downgraded 25% or more per sec
RSN_SYS_PX50R	REASON_ID_T	100	px downgraded 50% or more per sec
RSN_SYS_PX75R	REASON_ID_T	101	px downgraded 75% or more per sec
RSN_SYS_PXDGR	REASON_ID_T	102	px downgraded per sec
RSN_SYS_PXSRR	REASON_ID_T	103	px downgraded to serial per sec
RSN_SYS_GACRT	REASON_ID_T	104	global cache average CR get time
RSN_SYS_GACUT	REASON_ID_T	105	global cache ave current get time
RSN_SYS_LGONC	REASON_ID_T	106	current logons count
RSN_SYS_OPCSC	REASON_ID_T	107	current open cursors count
RSN_SYS_USLMP	REASON_ID_T	108	user limit %
RSN_SYS_SQSRT	REASON_ID_T	109	sql service response time
RSN_SYS_DBWTT	REASON_ID_T	110	database wait time ratio
RSN_SYS_DBCPT	REASON_ID_T	111	database cpu time ratio
RSN_SYS_RSPTX	REASON_ID_T	112	response time per txn
RSN_SYS_RCHTR	REASON_ID_T	113	row cache hit ratio
RSN_SYS_LCHTR	REASON_ID_T	114	library cache hit ratio
RSN_SYS_LCMSR	REASON_ID_T	115	library cache miss ratio
RSN_SYS_SPFRP	REASON_ID_T	116	shared pool free %
RSN_SYS_PGCHR	REASON_ID_T	117	pga cache hit %
RSN_SYS_PRCLP	REASON_ID_T	118	process limit %
RSN_SYS_SESLP	REASON_ID_T	119	session limit %
RSN_FIL_AFWT	REASON_ID_T	120	avg file write time
RSN_EVC_DTSW	REASON_ID_T	121	total time waited
RSN_SYS_RCMSR	REASON_ID_T	122	row cache miss ratio
RSN_RADL	REASON_ID_T	123	recovery area disk limit alerts
RSN_SYS_RDGNX	REASON_ID_T	124	redo generated per txn
RSN_SYS_CRRAR	REASON_ID_T	125	cr undo records applied per sec
RSN_SYS_THNTF	REASON_ID_T	126	threshold notice on system type
RSN_FIL_THNTF	REASON_ID_T	127	threshold notice on file type
RSN_EVC_THNTF	REASON_ID_T	128	threshold notice on event class
RSN_SVC_THNTF	REASON_ID_T	129	threshold notice on service
RSN_TBS_THNTF	REASON_ID_T	130	threshold notice on tablespace
RSN_SVC_CPUPC	REASON_ID_T	131	cpu time per user call
RSN_SES_THNTF	REASON_ID_T	132	threshold notice on sessions
RSN_SFBTS	REASON_ID_T	133	tablespace bytes based thresholds
RSN_SYS_INQPR	REASON_ID_T	134	instance should be quiesced
RSN_FAN_INSTANCE_UP	REASON_ID_T	135	instance up
RSN_FAN_INSTANCE_DOWN	REASON_ID_T	136	instance down
RSN_FAN_SERVICE_UP	REASON_ID_T	137	service up
RSN_FAN_SERVICE_DOWN	REASON_ID_T	138	service down
RSN_FAN_SERVICE_MEMBER_UP	REASON_ID_T	139	svc member up
RSN_FAN_SERVICE_MEMBER_DOWN	REASON_ID_T	140	svc member down
RSN_FAN_SVC_PRECONNECT_UP	REASON_ID_T	141	preconnect up
RSN_FAN_SVC_PRECONNECT_DOWN	REASON_ID_T	142	preconnect down
RSN_FAN_NODE_DOWN	REASON_ID_T	143	node down
RSN_FAN_ASM_INSTANCE_UP	REASON_ID_T	144	asm instance up
RSN_FAN_ASM_INSTANCE_DOWN	REASON_ID_T	145	asm instance down
RSN_FAN_DATABASE_UP	REASON_ID_T	146	database up
RSN_FAN_DATABASE_DOWN	REASON_ID_T	147	database down
RSN_SYS_DBTMR	REASON_ID_T	148	DB Time per Sec
RSN_SYS_XCNTR	REASON_ID_T	149	Executions Per Sec
RSN_STR_CAPTURE_ABORTED	REASON_ID_T	150	capture aborted
RSN_STR_APPLY_ABORTED	REASON_ID_T	151	apply aborted
RSN_STR_PROPAGATION_ABORTED	REASON_ID_T	152	propagation aborted
RSN_STR_STREAMSPOOL_FREE_PCT	REASON_ID_T	153	streams pool free
RSN_STR_ERROR_QUEUE	REASON_ID_T	154	new entry in error queue
RSN_LOG_ARCHIVE_LOG_GAP	REASON_ID_T	155	archived log gap for logminer
RSN_SYS_ACTVS	REASON_ID_T	156	average active sessions
RSN_SYS_SRLAT	REASON_ID_T	157	Avg synchronous single-blk read latency
RSN_SYS_IOMBS	REASON_ID_T	158	i/o megabytes
RSN_SYS_IOREQ	REASON_ID_T	159	i/o requests

Constants: Metric Names

sec =second

tx = transaction

Data Type = BINARY_INTEGER

Internal Name	External Name	Unit	Value
AVERAGE_FILE_READ_TIME	Average File Read Time	Microseconds	7000
AVERAGE_FILE_WRITE_TIME	Average File Write Time	Microseconds	7001
AVG_USERS_WAITING	Average Number of Users Waiting on a Class of Wait Events	Count of sessions	1000
BACKGROUND_CKPT_SEC	?	Number of Checkpoints	2033
BLOCKED_USERS	Number of Users blocked by some Session	Number of Users	4000
BRANCH_NODE_SPLITS_SEC	Branch Node Splits	Splits / sec	2085
BRANCH_NODE_SPLITS_TXN	Branch Node Splits	Splits / tx	2086
BUFFER_CACHE_HIT	Buffer Cache Hit	% of cache accesses	2000
CONSISTENT_CHANGES_SEC	Consistent Changes	Changes / sec	2073
CONSISTENT_CHANGES_TXN	Consistent Changes	Changes / tx	2074
CONSISTENT_GETS_SEC	Consistent Gets	Gets / sec	2069
CONSISTENT_GETS_TXN	Consistent Gets	Gets / tx	2070
CPU_TIME_PER_CALL	CPU time per user call per service	Microseconds per call	6001
CR_BLOCKS_CREATED_SEC	CR Blocks Created	Blocks / sec	2077
CR_BLOCKS_CREATED_TXN	CR Blocks Created	Blocks / tx	2078
CR_RECORDS_APPLIED_SEC	CR Undo Records Applied	Records / sec	2079
CR_RECORDS_APPLIED_TXN	CR Undo Records Applied	Records / tx	2080
CURSOR_CACHE_HIT	Cursor Cache Hit	% of soft parses	2050
DATABASE_CPU_TIME	Database CPU Time	% of all database time	2108
DATABASE_WAIT_TIME	Database Wait Time	% of all database time	2107
DB_BLKCHANGES_SEC	DB Block Changes	Changes / sec	2071
DB_BLKCHANGES_TXN	DB Block Changes	Changes / tx	2072
DB_BLKGETS_SEC	DB Block Gets	Gets / sec	2067
DB_BLKGETS_TXN	DB Block Gets	Gets / tx	2068
DB_TIME_PER_SEC	?	?	2123
DB_TIME_WAITING	Percent of Database Time Spent Waiting on a Class of Wait Events	% of Database time	1001
DBWR_CKPT_SEC	DBWR Checkpoints	Checkpoints per sec	2032
DISK_SORT_SEC	Sorts to Disk	Sorts / sec	2051
DISK_SORT_TXN	Sorts to Disk	Sorts / tx	2052
ELAPSED_TIME_PER_CALL	User call elapsed time for each service	Microseconds / call	6000
ENQUEUE_DEADLOCKS_SEC	Enqueue Deadlocks	Deadlocks / sec	2063
ENQUEUE_DEADLOCKS_TXN	Enqueue Deadlocks	Deadlocks / tx	2064
ENQUEUE_REQUESTS_SEC	Enqueue Requests	Requests / sec	2065
ENQUEUE_REQUESTS_TXN	Enqueue Requests	Requests / tx	2066
ENQUEUE_TIMEOUTS_SEC	Enqueue Timeouts	Timeouts / sec	2059
ENQUEUE_TIMEOUTS_TXN	Enqueue Timeouts	Timeouts / tx	2060
ENQUEUE_WAITS_SEC	Enqueue Waits	Waits / sec	2061
ENQUEUE_WAITS_TXN	Enqueue Waits	Waits / tx	2062
EXECUTE_WITHOUT_PARSE	Executes Performed Without Parsing	% of all executes	2054
EXECUTIONS_PER_SEC	Executions	Executions / sec	2121
FULL_INDEX_SCANS_SEC	Fast Full Index Scans per second	Scans / sec	2040
FULL_INDEX_SCANS_TXN	Fast Full Index Scans per transaction	Scans / tx	2041
GC_AVG_CR_GET_TIME	Global Cache CR Request	Milliseconds	2098
GC_AVG_CUR_GET_TIME	Global Cache Current Request	Milliseconds	2099
GC_BLOCKS_CORRUPT	Global Cache Blocks Corrupt	Blocks	2101
GC_BLOCKS_LOST	Global Cache Blocks Lost	Blocks	2102
HARD_PARSES_SEC	Hard Parses	Parses / sec	2046
HARD_PARSES_TXN	Hard Parses	Parse / tx	2047
LEAF_NODE_SPLITS_SEC	Leaf Node Splits	Splits / sec	2083
LEAF_NODE_SPLITS_TXN	Leaf Node Splits	Splits / tx	2084
LIBRARY_CACHE_HIT	Library Cache Hit	% of cache accesses	2112
LIBRARY_CACHE_MISS	Library Cache Miss	% of cache accesses	2113
LOGONS_CURRENT	Current Number of Logons	Number of Logons	2103
LOGONS_SEC	Cumulative Logons	Logons / sec	2018
LOGONS_TXN	Cumulative Logons	Logons / tx	2019
LONG_TABLE_SCANS_SEC	Scans on Long Tables	Scans / sec	2036
LONG_TABLE_SCANS_TXN	Scans on Long Tables	Scans / tx	2037
MEMORY_SORTS_PCT	Sorts in Memory	% of sorts	2001
NETWORK_BYTES_SEC	Network Bytes	Bytes / sec	2058
OPEN_CURSORS_CURRENT	Current Number of Cursors	Number of Cursors	2104
OPEN_CURSORS_SEC	Cumulative Open Cursors	Cursors sec	2020
OPEN_CURSORS_TXN	Cumulative Open Cursors	Cursors / tx	2021
PARSE_FAILURES_SEC	Parse Failures	Parses / sec	2048
PARSE_FAILURES_TXN	Parse Failures	Parses / tx	2049
PGA_CACHE_HIT	PGA Cache Hit Bytes	% processed (PGA)	2115
PHYSICAL_READS_DIR_SEC	Direct Physical Reads	Reads / sec	2008
PHYSICAL_READS_DIR_TXN	Direct Physical Reads	Reads / tx	2009
PHYSICAL_READS_LOB_SEC	Direct LOB Physical Reads	Reads / sec	2012
PHYSICAL_READS_LOB_TXN	Direct LOB Physical Reads	Reads / tx	2013
PHYSICAL_READS_SEC	Physical Reads	Reads / sec	2004
PHYSICAL_READS_TXN	Physical Reads	Reads / tx	2005
PHYSICAL_WRITES_DIR_SEC	Direct Physical Writes	Writes / sec	2010
PHYSICAL_WRITES_DIR_TXN	Direct Physical Writes	Writes / tx	2011
PHYSICAL_WRITES_LOB_SEC	Direct LOB Physical Writes	Writes / sec	2014
PHYSICAL_WRITES_LOB_TXN	Direct LOB Physical Writes	Writes / tx	2015
PHYSICAL_WRITES_SEC	Physical Writes	Writes / sec	2006
PHYSICAL_WRITES_TXN	Physical Writes	Writes / tx	2007
PROCESS_LIMIT_PCT	Process Limit Usage	% of maximum value	2118
PX_DOWNGRADED_SEC	Downgraded Parallel Operations (also 2093?)	Operations / sec	2090
PX_DOWNGRADED_25_SEC	Downgraded to 25% and more	Operations / sec	2087
PX_DOWNGRADED_50_SEC	Downgraded to 50% and more	Operations / sec	2088
PX_DOWNGRADED_75_SEC	Downgraded to 75% and more	Operations / sec	2089
PX_DOWNGRADED_SER_SEC	Downgraded to serial (also 2095?)	Operations / sec	2091
RB_RECORDS_APPLIED_SEC	Rollback Undo Records Applied	Records / sec	2081
RB_RECORDS_APPLIED_TXN	Rollback Undo Records Applied	Records / tx	2082
RECURSIVE_CALLS_SEC	Recursive Calls	Calls / sec	2028
RECURSIVE_CALLS_TXN	Recursive Calls	Calls / tx	2029
REDO_ALLOCATION_HIT	Redo Log Allocation Hit	% of redo allocations	2002
REDO_GENERATED_SEC	Redo Generated	Bytes / sec	2016
REDO_GENERATED_TXN	Redo Generated	Bytes / tx	2017
REDO_WRITES_SEC	Redo Writes	Writes/sec	2034
REDO_WRITES_TXN	Redo Writes	Writes / tx	2035
RESPONSE_TXN	Response	Secs / tx	2109
ROWS_CACHE_HIT	?	?	2110
ROWS_CACHE_MISS	?	?	2111
ROWS_PER_SORT	Rows Processed for each Sort	Rows / Sort	2053
SESS_LOGICAL_READS_SEC	Session Logical Reads	Reads / sec	2030
SESS_LOGICAL_READS_TXN	Session Logical Reads	Reads / tx	2031
SESSION_CPU_SEC	Database CPU	Microseconds / sec	2075
SESSION_CPU_TXN	Database CPU	Microseconds / tx	2076
SESSION_LIMIT_PCT	Session Limit Usage	% of maximum value	2119
SHARED_POOL_FREE_PCT	Shared Pool Free	% of shared pool	2114
SOFT_PARSE_PCT	Soft Parse	% of all parses	2055
SQL_SRV_RESPONSE_TIME	Service Response per execution	Seconds	2106
STREAMS_POOL_USED_PCT	Percent of the streams pool used		2136
TABLESPACE_BYT_FREE	Tablespace bytes free	KB Free	9001
TABLESPACE_PCT_FULL	Tablespace space usage	% Full	9000
TOTAL_INDEX_SCANS_SEC	Total Index Scans	Scans / sec	2042
TOTAL_INDEX_SCANS_TXN	Total Index Scans	Scans / tx	2043
TOTAL_PARSES_SEC	Total Parses	Parses / sec	2044
TOTAL_PARSES_TXN	Total Parses	Parses / tx	2045
TOTAL_TABLE_SCANS_SEC	Total Table Scans	Scans / sec	2038
TOTAL_TABLE_SCANS_TXN	Total Table Scans	Scans / tx	2039
USER_CALLS_PCT	User Calls	% of all calls	2056
USER_CALLS_SEC	User Calls	Calls / sec	2026
USER_CALLS_TXN	User Calls	Calls / tx	2027
USER_COMMITS_SEC	User Commits	Commits / sec	2022
USER_COMMITS_TXN	User Commits	Transactions / sec	2023
USER_LIMIT_PCT	User Limit Usage	% of maximum value	2105
USER_ROLLBACKS_SEC	User Rollbacks	Rollbacks / sec	2024
USER_ROLLBACKS_TXN	User Rollbacks	Rollbacks / tx	2025
USER_TRANSACTIONS_SEC	User transactions per second	?	2003

Constants: Object Types

Name	Value	Description
OBJECT_TYPE_EVENT_CLASS	4	Metrics collected on wait event class level. Currently supported metrics are AVG_USERS_WAITING and DB_TIME_WAITING
OBJECT_TYPE_FILE	2	Metrics collected on the file level. These are used for AVERAGE_FILE_READ_TIME and AVERAGE_FILE_WRITE_TIME metrics
OBJECT_TYPE_SERVICE	3	Metrics collected on the service level. Currently ELAPSED_TIME_PER_CALL and CPU_TIME_PER_CALL are collected
OBJECT_TYPE_SESSION	9	Metrics collected on the session level. Currently only BLOCKED_USERS is collected. The threshold can only be set at the instance level, which means that no object name should be specified when setting the threshold for this type of metric
OBJECT_TYPE_SYSTEM	1	Metrics collected on the system level for each instance
OBJECT_TYPE_TABLESPACE	5	Metrics collected on the tablespace level. Currently only TABLESPACE_PCT_FULL is collected

Constants: Relational Operator Types

Name	Value	Description
OPERATOR_CONTAINS	5	Value contained in a list of threshold values is considered a violation
OPERATOR_DO_NOT_CHECK	7	Don't apply default threshold to OBJECT_TYPE_TABLESPACE
OPERATOR_EQ	1	Value equal to the threshold one is a violation
OPERATOR_GE	4	Value greater or equal to the threshold is a violation
OPERATOR_GT	0	Value greater than the threshold is a violation
OPERATOR_LE	3	Value less or equal than the threshold is a violation
OPERATOR_LT	2	Value less than the threshold is a violation
OPERATOR_NE	6	Value not equal to the threshold one is a violation

Data Types

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));
/

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_SVRALRT_LIB
DBA_ALERT_HISTORY	DBMS_SYS_ERROR
DBA_OUTSTANDING_ALERTS	GV$ALERT_TYPES
DBA_THRESHOLDS	GV$DBFILE
DBMS_BACKUP_RESTORE	THRESHOLD_TYPE
DBMS_HA_ALERTS_PRVT	THRESHOLD_TYPE_SET
DBMS_PRVTAQIP	UTL_LMS
DBMS_SERVER_ALERT_PRVT	WRI$_ALERT_THRESHOLD

Value explanation

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

Warnings Enabled by Default

Recovery area low on free resources
Resumable session suspended
Snapshot too old
Tablespace Usage: warning = 85%, critical = 97%

Security Model

execute is granted to the dba and imp_full_database roles

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)
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 131
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);

Related Queries

Alert Types

set linesize 121
col object_type format a16
col group_name format a20
col internal_metric_category format a23
col internal_metric_name format a23

SELECT reason_id, object_type, type, group_name, scope, internal_metric_category, internal_metric_name
FROM gv$alert_types
ORDER BY 2,3;

Related Topics

DBMS_ALERT

DBMS_AQ

DBMS_AQADM

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us