Shawn_Walsh

Validating Oracle Connections

Blog Post created by Shawn_Walsh Employee on Nov 20, 2014

Out of box, Oracle DBMS 11.2 allows for a limited number of connections.

This can impact applications when the DBMS is used for multiple databases.

 

You can validate the settings with sqlplus:

 

sqlplus sys/password@serviceName AS SYSDBA

 

SQL>show parameter sessions;

 

NAME                                         TYPE   VALUE                                                                                               

-------------------------------------------------- ----------- --------------------------------------------

java_max_sessionspace_size        integer     0                                                                                                   

java_soft_sessionspace_limit         integer     0                                                                                                   

license_max_sessions                   integer     0                                                                                                   

license_sessions_warning              integer     0                                                                                                   

sessions                                       integer     247                                                                                                 

shared_server_sessions                 integer

 

 

Other helpful commands;

 

1. Check Current Setting of Parameters

sql> show parameter sessions

sql> show parameter processes

sql> show parameter transactions

 

 

2. If you're planning to increase "sessions" parameter you should also

plan to increase

"processes and "transactions" parameters.

A basic formula for determining  these parameter values is as follows:

  processes=x

  sessions=x*1.1+5

  transactions=sessions*1.1

 

 

3. These parameters cannot be modified in memory. We have to modify the

spfile only

(scope=spfile) and bounce the instance.

sql> alter system set processes=500 scope=spfile;

sql> alter system set sessions=555 scope=spfile;

sql> alter system set transactions=610 scope=spfile;

sql> shutdown abort

sql> startup

 

 

Troubleshooting:

 

 

4. To see how many sessions are currently in use:

 

 

     SELECT

      'Currently, '

      || (SELECT COUNT(*) FROM V$SESSION)

      || ' out of '

      || VP.VALUE

      || ' connections are used.' AS USAGE_MESSAGE

    FROM

      V$PARAMETER VP

    WHERE VP.NAME = 'sessions';

 

 

should return output like:

 

 

Currently, 31 out of 247 connections are used.

 

 

5. To see who is connected:

    select

           substr(a.spid,1,9) pid,

           substr(b.sid,1,5) sid,

           substr(b.serial#,1,5) ser#,

           substr(b.machine,1,6) box,

           substr(b.username,1,10) username,

    --       b.server,

           substr(b.osuser,1,8) os_user,

           substr(b.program,1,30) program

    from v$session b, v$process a

    where

    b.paddr = a.addr

    and type='USER'

    order by spid;

 

 

 

 

This will return  the OS and Database user names for each connection also.

Outcomes