CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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 [134 users online]    © 2010 psoug.org