Session details associated with Oracle SID

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
‘Session  Id………………………………………: ‘||s.sid,
‘Serial Num……………………………………….: ‘||s.serial#,
‘User Name ……………………………………….: ‘||s.username,
‘Session Status …………………………………..: ‘||s.status,
‘Client Process Id on Client Machine ………………..: ‘||’*’||s.process||’*’  Client,
‘Server Process ID ………………………………..: ‘||p.spid Server,
‘Sql_Address ……………………………………..: ‘||s.sql_address,
‘Sql_hash_value …………………………………..: ‘||s.sql_hash_value,
‘Schema Name ….. ………………………………..: ‘||s.SCHEMANAME,
‘Program  ………………………………………..: ‘||s.program,
‘Module ………………………………………….: ‘|| s.module,
‘Action ………………………………………….: ‘||s.action,
‘Terminal ………………………………………..: ‘||s.terminal,
‘Client Machine …………………………………..: ‘||s.machine,
‘LAST_CALL_ET …………………………………….: ‘||s.last_call_et,
‘S.LAST_CALL_ET/3600 ………………………………: ‘||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl(‘&sid’,s.sid)
/
set head on

Checking for Active Transactions SID
————————————
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops
————————————-
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

Session details with SPID
————————-
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/
To find Undo Generated For a given session
——————————————
select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid=’&sid’;

To list count of connections from other machines
————————————————
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

To get total count of sessions and processes
——————————————–
select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress
——————————-
select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;

To find sqltext for different sql hashvalue
——————————————-
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions
—————————————-
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;

To list inactive Sessions respective username
———————————————
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;

To find session id with set of SPIDs
————————————
select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533′);

To find Sql Text given SQLHASH & SQLADDR
—————————————-
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where  ADDRESS =’&addr’ order by piece;