Session Information SQL

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;




Forms stuck session
================

set lines 150

col MACHINE format a20


select s.sid,s.serial#, s.action,s.process clientprocess,s.machine, a.seconds_in_wait
from v$session_wait a, v$session s, v$process p
where a.sid=s.sid
and s.paddr = p.addr
and a.seconds_in_wait > 3000
and a.event = 'SQL*Net message from client'
and s.status = 'INACTIVE'
and s.action like 'FRM%'
order by a.SECONDS_IN_WAIT;

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 ;

Find OS process Id for a session
select spid, pid
from v$process
where addr = (select paddr from v$session where sid = );
Find the most recent sql by a session
select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid;
Who is blocking whome
select
(select username from v$session where sid=a.sid) blocker,
a.sid,’ is blocking ‘,
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
Identify corrupted objects using file# and block#

select segment_name from dba_segments where file_id= and between BLOCK_ID AND BLOCK_ID+BLOCKS-1;

Session information and tracing

 

Trace My session ID

select sys_context(’USERENV’,'SID’) from dual;
Oracle Session information user
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
order by spid;

SQL statement executed by a session

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

Find Objects locked by a session and the sql locking it

select c.owner,c.object_name,c.object_type,
b.sid,b.serial#,b.status,b.osuser,b.machine,s.sql_text from v$locked_object a ,v$session b,dba_objects c,v$sqltext_with_newlines s
where b.sid = a.session_id
and a.object_id = c.object_id and b.sql_address=s.address and b.sql_hash_value=s.hash_value;

Which session/sql is blocking what session/sql

select s1.sid||’,'||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||’,'||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

Enable sql trace with bind before executing a sql

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;

Enable sql trace with binds for a running session

alter session set TRACEFILE_IDENTIFIER = ‘APPS_MYTAG’;

EXEC dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE);

Other options

 exec dbms_system.set_int_param_in_session(&&sid,&&serial,’MAX_DUMP_FILE_SIZE’,2147483647);

exec sys.dbms_system.set_BOOL_param_in_session(94,239,’sql_trace’,true);
exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

To disable trace

exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

10 G onwards

SELECT   b.name, b.value_string, sq.sql_text
FROM     v$sql_bind_capture b, v$sql sq, v$session s
WHERE    sq.sql_id = b.sql_id
AND      sq.address = b.address
AND      sq.child_address = b.child_address
AND      sq.parsing_user_id = s.user#
AND      s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;

Forms stuck session
================

set lines 150

col MACHINE format a20


select s.sid,s.serial#, s.action,s.process clientprocess,s.machine, a.seconds_in_wait
from v$session_wait a, v$session s, v$process p
where a.sid=s.sid
and s.paddr = p.addr
and a.seconds_in_wait > 3000
and a.event = 'SQL*Net message from client'
and s.status = 'INACTIVE'
and s.action like 'FRM%'
order by a.SECONDS_IN_WAIT;