Standard DB Queries

Session details when SPID is known
=============================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where b.spid in( )
and a.paddr = b.addr;


Session details when CLIENT PROCESS is known
========================================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where a.process like '%%'
and a.paddr = b.addr;


Wait Event details when SPID is known
=================================

select a.sid,a.serial#,a.status,(sysdate-logon_time)*24*60 MINSDURATN,b.event,b.p1,b.p2,b.p3
from v$session a ,v$session_wait b
where
a.spid= and
a.sid=b.sid ;


LongOps (Long Operations)
======================

• select sid, serial#, opname, qcsid, to_char(start_time,'DD-MON-YYYY HH24:MI:SS') "start", (sofar/totalwork)*100 "%_complete" from v$session_longops order by sid, start_time;
• Same query with Null and Zeros taken care of
  

select sid, serial#, opname, qcsid,
to_char(start_time,'DD-MON-YYYY HH24:MI:SS') "start",
(sofar/decode(totalwork,0,1,totalwork))*100 "%_complete"
from v$session_longops where
sid=1922 and
((sofar/decode(totalwork,0,1,totalwork))*100) <> 100 and
((sofar/decode(totalwork,0,1,totalwork))*100) <> 0
order by sid, start_time;