Oracle SID session query

Published by Alessandro Caredda on 21 March 202021 March 2020

In the Oracle SID session query article talking about of Query to retrieve information about Oracle sessions having the session SID.

This query is widely used by me when I have to analyze sessions that are creating problems, so if we have to identify, for example, a session that is generating LOCK, once the SID has been identified, we can acquire further information from the query below.

We can identify the time when the session was connected and the time when the last call of the statement was made.

Moreover, we can also retrieve which instance the session comes from, in case you are working on a RAC. The session’s SQL_ID is retrieved, that is, the identified code of any statement that is executed in the database. In addition to the operating system process, the program from which the session originates and the schema, we have 3 fundamental codes:

The SID of the session, which without the SERIAL would not indicate the uniqueness of the session and the SPID or the process of the operating system linked to the session. Finally other information relating to the user of the DB and the OS and the command executed.

Moreover, we see below the detail of each field called by the query. In our query we select the fields:

Columns retrieved by the query:

LOGON_TIME – Indicates the time when the session started.
STATUS – The state of the session if ACTIVE or INACTIVE.
INST_ID – In the case of a RAC (Real Application Cluster), the number of the instance where the session is present.
SQL_ID – The identification code of the query.
PROCESS – The process of the operating system.
PROGRAM – The client you are using (Toad, SQL Developer,) In addition to the process of the operating system, the program from which the session originates and the schema, we have 3 fundamental codes: The SID of the session, which without the SERIAL would not indicate the uniqueness of the session and the SPID or the process of the operating system linked to the session.

Finally other information relating to the user of the DB and the OS and the command executed.

We see below the detail of each field called by the query. In our query we select the fields:
SCHEMANAME – The user with the Oracle session has started.
SID – The identification code of the Oracle session.
SERIAL # – The unique serial code of an Oracle session.
SPID – Process of the operating system tied to the Oracle session.
OSUSER – The user of the operating system with which the Oracle session started.
MACHINE – The name of the client from where the Oracle session starts.
TERMINAL – The name of the operating system from where the Oracle session starts.
LAST_CALL_ET – The time the session changed from ACTIVE to INACTIVE and vice versa.
USERNAME – Name of the Oracle user.
COMMAND – The command that was executed on the Oracle databas

Oracle SID Session Query:

To extrapolate this information from the Oracle database, we have linked 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 V $ SESSION view to the DBA_USERS view through the JOIN S.USER # = U.USER_ID.

Finally after the relationship of the tables V $ PROCESS, V $ SESSION and DBA_USERS, to take the information we have selected, we use the condition SID = the SID number of the Oracle session.

Oracle SID 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, p.pid, 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.sid ='&sid';

To view my guides on CRS commands click here! At this LINK, however, you will find SQL queries

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin

For any doubt, do not hesitate to comment on the article.