Script to Check Maximum Processes

 
Instead of querying the v$parameter or executing the show parameter command then querying the v$process dynamic view. This query will return the maximum and current utilization of processes on the database. Easier to check if your database is hitting ORA-00020: maximum number of processes.

select resource_name,
current_utilization/limit_value*100,
current_utilization, max_utilization,
limit_value
from v$resource_limit
where resource_name in ('processes','sessions');

However there is a certain version of Oracle 9i that has a bug regarding the v$resource_limit view. Bug 3896119 – CURRENT_UTILIZATION of V$RESOURCE_LIMIT may be too high. So you might want to manually check by counting v$process or use the script below.

select count(addr) as CURRENT_VALUE,
parameter.name,
parameter.value as MAX_VALUE,
count(addr)/parameter.value *100 as PCT_USED
from v$process,
(select name,
value
from v$parameter
where name = 'processes') parameter
group by name, value;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart