Oracle User Sessions Query

Published by Alessandro Caredda on 7 March 20237 March 2023

With the article “Oracle User Sessions Query”, we retrieve information about the LOGON, STATUS, SID, software, and machine of all Oracle sessions for a particular user.

The first lines of the query concern the formatting of the columns and will help us have a better visualization of the output as we assign width to each column.

This will greatly assist you in reading the output.

The fields that we will select are:

LOGON_TIME – Indicates the time when the session started.
STATUS – The status of the session, whether it is ACTIVE or INACTIVE.
INST_ID – In the case of a RAC (Real Application Cluster), the instance number where the session is present.
SQL_ID – The identifying code for the query.
PROCESS – The operating system process.
PROGRAM – The client you are using (Toad, SQL Developer, etc.)
SCHEMANAME – The user who initiated the Oracle session.

SID – The identifying code for the Oracle session.
SERIAL# – The unique serial code for an Oracle session.
SPID – The operating system process associated with the Oracle session.
OSUSER – The operating system user with whom the Oracle session started.
MACHINE – The name of the client from which the Oracle session starts.
TERMINAL – The name of the operating system from which the Oracle session starts.
LAST_CALL_ET – The time when the session changed status from ACTIVE to INACTIVE and vice versa.
USERNAME – Oracle user name.
COMMAND – The command that was executed on the Oracle database.

The tables from which we extract the data

To extract this information from the Oracle database, we have related three tables:

GV$SESSION
GV$PROCESS
DBA_USERS

Also, in the WHERE condition, we first related the views
V$PROCESS and
V$SESSION
through the JOIN
P.ADDR=S.PADDR
and related the view V$SESSION
with the view DBA_USERS
through the JOIN
S.USER#=U.USER_ID.

Finally, after the relationship of the V$PROCESS, V$SESSION, and DBA_USERS tables,
to check the information we selected for a specific user,
we pass the condition
S.USER# = the user’s name.

Oracle user session query

Set lines 300
Set pages 60
Col LAST_CALL for a18
Col LOGON_TIME for a18
Col schemaname for a16
Col command for a24
Col machine for a16
Col action for a12
Col username for a16
Col osuser for a16
Col terminal for a16
select to_char(S.logon_time,'DD-MON-YYYY hh24.mi.ss') LOGON_TIME, s.status, s.inst_id, s.sql_id, 
s.process, s.program, s.schemaname, s.sid, s.serial#, p.spid, s.osuser, 
S.machine, S.terminal,to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL ,  U.username, S.ACTION, decode(S.command,0, 'No command in progress.', 1, 'CREATE TABLE',2, 'INSERT', 3, 'SELECT', 4, 'CREATE CLUSTER', 5, 'ALTER CLUSTER', 6, 'UPDATE', 7, 'DELETE') COMMAND 
 from gv$session S, dba_users U, gv$process P
where P.ADDR = S.PADDR and 
      S.user# = U.user_id and 
      s.schemaname='&user'
order by LOGON_TIME;

To view my guides on CRS Commands, click here!
Instead, you will find SQL queries at this LINK.

Stay up to date on our activities by joining our community:
Facebook
Youtube
Linkedin

If you have any questions, do not hesitate to comment on the article.