Query to get session details in oracle database

Many times DBA need to look at the session details for analysis. The details include sql_id, stats, locking, wait events. Here I am giving Query to get session details in oracle database, how to get the sql text from sid in oracle, how to check inactive sessions in oracle, how to check active sessions in oracle and other queries which will help in troubleshooting

Query to get session details in oracle

col sid format 9999
col pid format 9999
col serial# format 99999
col process format a8 heading "unixPID"
col spid format a8 heading "unixPID"
col username format a9
col addr format a11
col program format a20 trunc
col logon_time format a18
col osuser format a8 heading unixUsr
col p_user format a9 heading unixUsr
col terminal format a7 heading unixtrm
col command format 99 heading Cd
col machine format a7
col action format a7
col module format a10
set pagesize 24
prompt "Enter the Oracle Session ID (SID) user in question"
select p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module,
s.osuser,
s.terminal
from v$process p,
v$session s
where s.sid = &sid
and p.addr = s.paddr
order by s.logon_time
/

how to get the sql text from sid in oracle

set verify off pages 50000 linesize 80 heading off feedback off
col sid noprint
col serial# noprint
col rec_num noprint
col line_num noprint
col text format a80 word
select ses.sid, ses.serial#, 10 rec_num, 1 line_num,
rpad('=',80,'=')
||rpad('SID : '||ses.sid,40)
||rpad('Serial# : '||ses.serial#,40)
||rpad('Audit SID : '||ses.audsid,40)
||rpad('Status : '||ses.status,40)
||rpad('DB User : '||ses.username,40)
||rpad('OS User : '||ses.osuser,40)
||rpad('Module : '||ses.module,400)
||rpad('Action : '||ses.action,40)
||rpad('OS Server PID: '||prc.spid,40)
||rpad('OS Client PID: '||ses.process,40)
||rpad('Executions : '||sqa.executions,40)
||rpad('Rows Proc : '||sqa.rows_processed,40)
||rpad('Disk Reads : '||sqa.disk_reads,40)
||rpad('Buffer Gets : '||sqa.buffer_gets,40)
||rpad('LIOs/Row : '||round(sqa.buffer_gets/decode(sqa.rows_processed,0,1,sqa.rows_processed),2),40)
||rpad('LIOs/Exec : '||round(sqa.buffer_gets/decode(sqa.executions,0,1,sqa.executions),2),40)
||rpad('Event : '||wat.event,80)
||rpad('Machine : '||ses.machine,40)
||rpad('Logon Time : '||to_char(ses.logon_Time,'MM/DD/YY HH24:MI'),40)
||rpad('Statement : ',80) text
from v$sqlarea sqa
, v$process prc
, v$session ses
, v$session_wait wat
where sqa.address = ses.sql_address
and sqa.hash_value = ses.sql_hash_value
and prc.addr = ses.paddr
and ses.type != 'BACKGROUND'
and ses.username is not null
and ses.audsid != userenv('SESSIONID')
and wat.sid(+) = ses.sid
and ses.sid = &1
union
select ses.sid, ses.serial#, 20 rec_num, piece line_num
, sql_text text
from v$sqltext txt
, v$session ses
where txt.address = ses.sql_address
and txt.hash_value = ses.sql_hash_value
and ses.type != 'BACKGROUND'
and ses.username is not null
and ses.audsid != userenv('SESSIONID')
and ses.sid = &1
order by 1, 2, 3, 4
/

Query to find the wait history for the particular SID

set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &1
/

To find out the wait events for this session

SELECT sample_time, event, wait_time,sql_id
FROM gv$active_session_history
WHERE session_id = &1
AND session_serial# = &2;

To find old recent sample times

SELECT sample_time
from gv$active_session_history
WHERE session_id = 147
AND sample_time > SYSDATE-10/1440
ORDER BY 1;

 
How to check the sql query history of the session

SELECT ash.sql_id
, ash.sql_child_number
--, s.sql_text
, ash.sql_exec_start
, ash.sql_exec_id
, TO_CHAR(MIN(ash.sample_time),'hh24:mi:ss') AS min_sample_time
, TO_CHAR(MAX(ash.sample_time),'hh24:mi:ss') AS max_sample_time
FROM v$active_session_history ash
, v$sql s
WHERE ash.sql_id = s.sql_id (+)
AND ash.sql_child_number = s.child_number (+)
AND ash.session_id = &1
AND ash.session_serial# = &2
GROUP BY
ash.sql_id
, ash.sql_child_number
--, s.sql_text
, ash.sql_exec_start
, ash.sql_exec_id
ORDER BY
MIN(ash.sample_time);

Query to check CPU consumption of the session

select s.value
from v$sesstat s, v$statname n
where s.sid = &1
and n.statistic# = s.statistic#
and n.name = 'CPU used by this session'

Query to check all the stats for the session

select name, value
from v$sesstat s, v$statname n
where sid = &1
and n.statistic# = s.statistic#
order by value desc
/

Query to find what sid is doing a full table scan

column user_process heading "Name |SID" format a20;
column long_scans heading "Long Scans" format 999,999,999;
column short_scans heading "Short Scans" format 999,999,999;
column rows_retreived heading "Rows Retrieved" format 999,999,999;
set linesize 1000
set timing on
select ss.username||'('||se.sid||') ' "USER_PROCESS",
sum(decode(name,'table scans (short tables)',value)) "SHORT_SCANS",
sum(decode(name,'table scans (long tables)', value)) "LONG_SCANS",
sum(decode(name,'table scan rows gotten',value)) "ROWS_RETRIEVED"
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
order by LONG_SCANS desc
/

To Find  Historical Plans from the AWR Using SQLID

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))
/

Query to show the Bind Variable for a Given SQLID.

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT
sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id='&sqlid'
/

How to check whether stats are current for the objects involved in SQL query

set line 1000
set verify off
col owner format a15
col object_name format a25
col object_type format a12
col "LAST ANALYZED" format a13
select do.OWNER,do.OBJECT_NAME,OBJECT_TYPE,
decode (OBJECT_TYPE,'TABLE' , (Select LAST_ANALYZED from dba_tables where owner=do.owner and TABLE_NAME=do.object_name) ,
'INDEX' , (Select LAST_ANALYZED from dba_indexes where owner=do.owner and INDEX_NAME=do.object_name) ,
'UNKNOWN') "LAST ANALYZED",STATUS
from DBA_OBJECTS do
where OBJECT_TYPE in ('TABLE','INDEX')
and (OWNER,OBJECT_NAME) in (select OBJECT_OWNER,OBJECT_NAME from V$SQL_PLAN where HASH_VALUE=&1)
/

how to find sql query using sql_id in oracle

col sql_sql_text head SQL_TEXT format a150 word_wrap
col sql_child_number head CH# for 999
select
hash_value,
plan_hash_value,
child_number sql_child_number,
sql_text sql_sql_text
from
v$sql
where
sql_id = ('&1')
and child_number like '&2'
order by
sql_id,
hash_value,
child_number
/

how to check inactive sessions in oracle

select sid, serial#, status, username , module, form
from v$session s where status like '%INACTIVE%';

how to check Active sessions in oracle

select sid, serial#, status, username , module, form
from v$session s where status like 'ACTIVE';

how to check the number of connections in oracle

For Non-RAC

select status, count(*) from v$session group by status;

For RAC

select inst_id,status, count(*) from v$session group by inst_id, status;

Hope you like these queries

alter system kill session: We can kill the oracle session using alter system kill session in the Oracle database. Sessions are marked for the kill if not killed immediately
oracle kill own session privilege: oracle database does not provide any direct answer to oracle kill own session privilege but this can be achieved with the help of procedure
Active Session History: Check out about Active Session History, how it is configured, how to find performance bottlenecks using ASH, ASH report generation, ASH queries
Oracle Database Health check: check out important Oracle Database Health check for troubleshooting problems, unusual behavior in oracle database
Scripts to check locks in oracle database: Scripts to check locks in oracle database, unlock oracle table, find session holding oracle table locks, check the lock on the package
How to check temp tablespace in Oracle: check out How to check temp tablespace in Oracle, How to resize the tempfile, how to drop the tempfile, How to check the temp usage by Session
how to get table definition in oracle: Check out how to get table definition in oracle, oracle show index definition, get ddl of a materialized view in oracle, get the query of a view in oracle