SQL> select status,count(*) from v$session group by status;
STATUS COUNT(*)
-------- ----------
ACTIVE 43
INACTIVE 354
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------------ ------------------------------
processes 403 435 500 500
sessions 421 530 792 792
SQL> select username,status,count(*) from v$session group by username,status;
USERNAME STATUS COUNT(*)
------------------------------ -------- ----------
PROD_101 INACTIVE 27
CRM_USER INACTIVE 3
SYS ACTIVE 1
ORDM INACTIVE 1
DBSNMP INACTIVE 2
ORDM_TEST INACTIVE 38
KZAENGER INACTIVE 4
RTALBOOM INACTIVE 1
CPINEDA INACTIVE 1
GROMERO INACTIVE 2
ORDM_UAT INACTIVE 275
PUBLIC INACTIVE 1
12 rows selected.
SQL> select count(*) from v$session where last_call_et>3600 and username='ORDM_UAT';
COUNT(*)
----------
202
From the above result we can understand that there are 202 sessions are in inactive state more than half an hour.
If we kill the sessions then the issue will resolved (But confirm with the application team before killing the sessions)
STATUS COUNT(*)
-------- ----------
ACTIVE 43
INACTIVE 354
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------------ ------------------------------
processes 403 435 500 500
sessions 421 530 792 792
SQL> select username,status,count(*) from v$session group by username,status;
USERNAME STATUS COUNT(*)
------------------------------ -------- ----------
PROD_101 INACTIVE 27
CRM_USER INACTIVE 3
SYS ACTIVE 1
ORDM INACTIVE 1
DBSNMP INACTIVE 2
ORDM_TEST INACTIVE 38
KZAENGER INACTIVE 4
RTALBOOM INACTIVE 1
CPINEDA INACTIVE 1
GROMERO INACTIVE 2
ORDM_UAT INACTIVE 275
PUBLIC INACTIVE 1
12 rows selected.
SQL> select count(*) from v$session where last_call_et>3600 and username='ORDM_UAT';
COUNT(*)
----------
202
From the above result we can understand that there are 202 sessions are in inactive state more than half an hour.
If we kill the sessions then the issue will resolved (But confirm with the application team before killing the sessions)
No comments:
Post a Comment