How to Find Sessions in RAC Oracle All in One

December 9, 2020

How to Find Sessions In RAC Oracle All in One

To find sessions in databases will be helpful to evaluate interim growth in sessions, to tune the database’s performance, to monitor application connected sessions, etc. Here we are going to show various scenarios to find connected sessions in RAC databases. Here we have a two-node RAC to perform SQL commands. Below are SQL queries with many options.:

Connected sessions details for each SID in all nodes, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
  
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN LOCKWAIT FORMAT A9
COLUMN PROGRAM FORMAT A27
COLUMN SID FORMAT 9999
COLUMN SERIAL# FORMAT 99999
COLUMN SPID FORMAT A5
COLUMN LOGON_TIME FORMAT A21
COLUMN STATUS FORMAT A11
COLUMN MODULE FORMAT A21
COLUMN MACHINE FORMAT A21
COLUMN  LAST_CALL_ET FORMAT 99999999
 
SELECT S.SCHEMANAME , S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.LOCKWAIT, S.STATUS, S.MODULE, S.MACHINE, S.PROGRAM, TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME, S.LAST_CALL_ET
FROM   GV$SESSION S, GV$PROCESS P
WHERE  S.PADDR = P.ADDR AND S.INST_ID = P.INST_ID ORDER BY S.SCHEMANAME, S.OSUSER, S.INST_ID;

Connected sessions of an individual SCHEMA from all nodes, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
  
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN LOCKWAIT FORMAT A9
COLUMN PROGRAM FORMAT A27
COLUMN SID FORMAT 9999
COLUMN SERIAL# FORMAT 99999
COLUMN SPID FORMAT A5
COLUMN LOGON_TIME FORMAT A21
COLUMN STATUS FORMAT A11
COLUMN MODULE FORMAT A21
COLUMN MACHINE FORMAT A21
COLUMN  LAST_CALL_ET FORMAT 99999999

SELECT S.SCHEMANAME , S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.LOCKWAIT, S.STATUS, S.MODULE, S.MACHINE, S.PROGRAM, TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME, S.LAST_CALL_ET
FROM   GV$SESSION S, GV$PROCESS P
WHERE  S.PADDR = P.ADDR AND S.INST_ID = P.INST_ID AND S.SCHEMANAME ='DEMOPLS' ORDER BY S.SCHEMANAME, S.OSUSER, S.INST_ID;

NOTE: You can also use the below option in case if you don’t want to hardcode SCHEMANAME in the where clause.

S.SCHEMANAME =’&SCHEMANAME’

Total Connected sessions from each node, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333

SELECT INST_ID,COUNT(*) "CONNECTED SESSION ON EACH NODE" FROM GV$SESSION GROUP BY INST_ID ORDER BY INST_ID;

Total ACTIVE sessions of all schemas from all nodes, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
 
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN LOCKWAIT FORMAT A9
COLUMN PROGRAM FORMAT A27
COLUMN SID FORMAT 9999
COLUMN SERIAL# FORMAT 99999
COLUMN SPID FORMAT A5
COLUMN LOGON_TIME FORMAT A21
COLUMN STATUS FORMAT A11
COLUMN MODULE FORMAT A25
COLUMN MACHINE FORMAT A25
COLUMN  LAST_CALL_ET FORMAT 99999


SELECT S.SCHEMANAME , S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.LOCKWAIT, S.STATUS, S.MODULE, S.MACHINE, S.PROGRAM, TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME, S.LAST_CALL_ET
FROM   GV$SESSION S, GV$PROCESS P
WHERE  S.PADDR   = P.ADDR AND    S.INST_ID = P.INST_ID AND S.STATUS = 'ACTIVE' ORDER BY S.SCHEMANAME, S.OSUSER, S.INST_ID;

Total ACTIVE sessions from each node, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333

SELECT INST_ID, STATUS, COUNT(*) "ACTIVE SESSIONS ON EACH NODE" FROM GV$SESSION 
WHERE STATUS= 'ACTIVE' GROUP BY INST_ID, STATUS ORDER BY INST_ID;

Total INACTIVE sessions of all schemas from all nodes, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
 
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN LOCKWAIT FORMAT A9
COLUMN PROGRAM FORMAT A27
COLUMN SID FORMAT 9999
COLUMN SERIAL# FORMAT 99999
COLUMN SPID FORMAT A5
COLUMN LOGON_TIME FORMAT A21
COLUMN STATUS FORMAT A11
COLUMN MODULE FORMAT A25
COLUMN MACHINE FORMAT A25
COLUMN  LAST_CALL_ET FORMAT 99999


SELECT S.SCHEMANAME , S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.LOCKWAIT, S.STATUS, S.MODULE, S.MACHINE, S.PROGRAM, TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME, S.LAST_CALL_ET FROM   GV$SESSION S, GV$PROCESS P
WHERE  S.PADDR   = P.ADDR AND    S.INST_ID = P.INST_ID AND S.STATUS = 'INACTIVE' ORDER BY S.SCHEMANAME, S.OSUSER, S.INST_ID;

Total INACTIVE sessions from each node, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333

SELECT INST_ID, STATUS, COUNT(*) "ACTIVE SESSIONS ON EACH NODE" FROM GV$SESSION 
WHERE STATUS= 'INACTIVE' GROUP BY INST_ID, STATUS ORDER BY INST_ID;

Total connected sessions of each user from all nodes, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
COL SCHEMANAME FOR A11

SELECT INST_ID,SCHEMANAME, COUNT(*) "TOTAL CONNECTED SESSIONS OF USERS" FROM V$SESSION GROUP BY INST_ID, SCHEMANAME ORDER BY SCHEMANAME;

Total connected sessions more than 60 minutes for all node, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
 
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN LOCKWAIT FORMAT A9
COLUMN PROGRAM FORMAT A27
COLUMN SID FORMAT 9999
COLUMN SERIAL# FORMAT 99999
COLUMN SPID FORMAT A5
COLUMN LOGON_TIME FORMAT A21
COLUMN STATUS FORMAT A11
COLUMN MODULE FORMAT A25
COLUMN MACHINE FORMAT A25
COLUMN  LAST_CALL_ET FORMAT 99999

SELECT S.SCHEMANAME , S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.LOCKWAIT, S.STATUS, S.MODULE, S.MACHINE, S.PROGRAM, TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME, S.LAST_CALL_ET FROM   GV$SESSION S, GV$PROCESS P
WHERE  S.PADDR   = P.ADDR AND    S.INST_ID = P.INST_ID AND S.LAST_CALL_ET > 3600 ORDER BY S.SCHEMANAME, S.OSUSER, S.INST_ID;

NOTE: Here you can change the value of S.LAST_CALL_ET according to your requirement and the value of this column is in seconds.

Total connected sessions coming from each machine of schema, click here for sample output.

SET PAUSE ON
SET PAUSE 'HIT RETURN TO CONTINUE POWERED BY DBsGuru **Share Learn Grow**'
SET PAGESIZE 51
SET LINESIZE 333
  
COLUMN INST_ID FORMAT 9
COLUMN SCHEMANAME FORMAT A11
COLUMN MACHINE FORMAT A25

SELECT INST_ID,SCHEMANAME,MACHINE,COUNT(*) "MACHINE WISE SCHEMA COUNT" FROM GV$SESSION
WHERE SCHEMANAME='TESTUSER' GROUP BY INST_ID,SCHEMANAME,MACHINE ORDER BY INST_ID,SCHEMANAME,MACHINE;

Above we tried to capture all options to find connected sessions in RAC but still we have many more options can be used which depends on requirements. According to requirements, you can change columns in the where clause.

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Click here for step by step Connected Sessions along with Network Authentication in Oracle

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

ASM, DATABASE, ORACLE, RAC, SCRIPTS, TUNING

How to Find Sessions In RAC Oracle All in One

Related articles