Wednesday, January 31, 2018

Oracle Concurrent connections limit

The number of sessions/concurrent connections the database (oracle) was configured to allow:
    SELECT name, value FROM v$parameter WHERE name = 'sessions';

The number of sessions currently active:
    SELECT COUNT(*) FROM v$session;

Complete Query:
    SELECT
      'Currently, ' 
      || (SELECT COUNT(*) FROM V$SESSION)
      || ' out of ' 
      || VP.VALUE 
      || ' connections are used.' AS DATA
    FROM 
      V$PARAMETER VP
    WHERE VP.NAME = 'sessions';

OUTPUT:
DATA
Currently, 93 out of 172 connections are used.

Resource Name and Connections:
    select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

Want to increase the concurrent connections/sessions, open init.ora file inside oracle setup and increase "processes, cursor_limit and sessions".
However this change require Oracle Restart.

No comments:

Post a Comment