DESCRIPTION
This alert calculates the percent of sessions used, i.e. the number of current sessions divided by the maximum allowed sessions (SESSIONS parameter).
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Percent of Sessions Used
Execution Interval: 10 Minutes
Notification Text: You are getting close to running into the maximum allowed sessions (SESSIONS parameter).
SQL Statement:
select 100 * num_sessions / max_sessions pct_sessions_used
from (
select p.value max_sessions, count(1) num_sessions
from v$session s, v$parameter p
where p.name='sessions'
group by p.value)
Execute Against: Monitored Instance
Units: Session Usage %
High Threshold: Min 90, Max empty
Medium Threshold: Min 80, Max 90