Once in a while, there might be a need to monitor number of processes/sessions from an application, especially when there is a version update of the application to make sure there is no session leak etc. — a serious issue which could lead the process/session limit are reached and Oracle rejects new connections — literally the system is down. For Oracle EE, you can use the static view DBA_HIST_RESOURCE_LIMIT which contains snapshots from V$RESOURCE_LIMIT. But with Oracle SE, DBA_HIST_RESOURCE_LIMIT is not populated without AWR. Although you can still get session usage information from DBA_HIST_SYSMETRIC_SUMMARY with METRIC_ID=2143 (session metric ID from V$METRICNAME), it might just be a good idea to just use your own script to take snapshots from V$RESOURCE_LIMIT and other dynamic views like V$SESSION so that you have more control of the frequency of snapshots and more information about sessions (from which program, machine etc.)

Below are what I have done for a few systems:

1. Create two history tables with a timestamp column to hold snapshots from V$RESOURCE_LIMIT, V$SESSION, V$PROCESS.

create table service_db_sess_his as select vrl.*, systimestamp CREATED_TIME 
from v$resource_limit vrl 
where 1=2;

create table service_db_sess_his_detail
as SELECT count(s.sid) as count, SUM(pga_alloc_mem)/1024/1024 AS "Mbytes allocated", SUM(pga_used_mem)/1024/1024 AS "Mbytes used", s.machine, s.program, systimestamp created_time
FROM v$process p, v$session s 
WHERE s.paddr( + ) = p.addr and 1=2
group by s.machine, s.program;

2. Create your own script (BASH, PL/SQL, Python, Perl whatever you like) to populate two history tables from three views above with the following statements:

delete from service_db_sess_his where CREATED_TIME<sysdate-14;
insert into service_db_sess_his select vrl.*, systimestamp 
from v$resource_limit vrl where RESOURCE_NAME in ('processes','sessions');
commit;

delete from service_db_sess_his_detail where CREATED_TIME<sysdate-14;
insert into service_db_sess_his_detail select count(s.sid), SUM(pga_alloc_mem)/1024/1024, SUM(pga_used_mem)/1024/1024, s.machine, s.program, systimestamp
FROM v$process p, v$session s 
WHERE s.paddr( + ) = p.addr
group by s.machine, s.program
having count(s.sid)>0 order by 1;
commit;

Delete statement is to maintain the size of the tables. Here I keep 14 days data within them. Adjust the value as needed.

3. Schedule your script either as an OS job or an Oracle internal job to run regularly, for example every 15 minutes to take a snapshot.

If something happens, two history tables should give you some basic information to start your troubleshooting journey. Don’t forget to drop tables when they are not needed anymore.

• Oracle