Oracle session details and query to find out details

Hi DBA-Mates,
Many times we all faced issues like blocking session, High Temp usage and etc. which is related to session.
As we all knowing that every login requires a session. It specifies the maximum number of the sessions that can be created in the system. 

Actually, this parameter determines the maximum number of concurrent users in the system.

It can set this parameter explicitly by to a value almost closed to our maximum number of the concurrent users plus the number of background processes and approx 10% of the recursive sessions.

By default value for this parameter is as its minimum and values between 1 and the default do no trigger error. But always remember oracle ignores them and uses the default instead.

Always remember that the default values of ENQUEUE_RESOURCES and TRANSACTIONS parameters are also derived from SESSIONS. Therefore, if you increase the value of SESSIONS , you also should consider whether to adjust the values of above two parameters ENQUEUE_RESOURCES and TRANSACTIONS.

This sql query displays information about a session.
For this query, we should have SID number available.

column sid                                format 9999
column serial                             format 99999
column osuser   HEADING 'O/S|Username'    FORMAT a9
column username HEADING 'Oracle|Username' FORMAT a9
column program                            FORMAT a20

SELECT sid, serial#, osuser, username, status, logon_time, program
FROM v$session
WHERE sid like '&SID'
/

And some times, if the session is INACTIVE form session, we can kill the session.
In case of Active or Inactive Toad connections , SQL/PLSQL session can be killed but remember always if you are working on production, we must should follow some points like:
1. we should collect the details related to that particular session.
2. Inform the concern team.
3. Get approval.
4. Now you are ready to kill the session.

A session can be killed by below command:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

But in RAC, its is different because in RAC servers we use gv$session. And to kill the session we should use below command:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

If you want to kill the session Immediate because as we know this kill command will asks session to kill itself. But in any situation if you want to kill immediately, So better to use IMMEDIATE clause as shown below command.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Also from this below session query, we can get the information about the sessions.

It will ask for some values which can be escape if you are not knowing and hit enter.

Select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||

       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||

       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||

       s.machine||'  -  '|| s.terminal||chr(10)||

       '        OS Process Ids : '||

       s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||

       '   Client Program Name : '||s.program "Session Info"

  from v$process p,v$session s

 where p.addr = s.paddr

   and s.sid = nvl('&SID',s.sid)

   and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))

   and s.process = nvl('&Process',s.process)

   and p.spid = nvl('&spid',p.spid)

   and s.username = nvl('&username',s.username)

   and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))

   and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))

   and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',

       nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'

/

Hope you learned something new here. Please let us know for any concerns or suggestions either in comment box or Contact US. Remember we loved to listen from you.

Hope these will be useful:
How to Read and Write UTL file!!
EBS R12 Installation Steps
ORACLE DATABASE
Rebuild Context file Steps
Regards,
ora-data Team