Oracle : HOW TO TRACE SESSIONS
==========================================================
For TRACING your own session , use the following query to find details on your own session:
==========================================================
set linesize 132
set pagesize 1000
set echo on
col rbs format a6
col oracle_user format a12 heading “Oracle|Username”
col sid format 99999 heading “Oracle|SID”
col serial# format 99999 heading “Oracle|Serial#”
col unix_pid format a6 Heading “Unix|PID”
col Client_User format a12 Heading “Client|Username”
col Unix_user format a12 Heading “Unix|Username”
col Logon_time format a11 Heading “Login|Time”
col lock_wait format a4 heading “Lock|Wait”
select
s.username oracle_user,
s.osuser client_user,
p.username unix_user,
s.status status,
s.sid sid,
s.serial# serial#,
p.spid unix_pid,
to_char(s.logon_time,’MM-DD HH24:MI’) logon_time,
decode(s.lockwait,NULL,’No’,’Yes’) lock_wait
from v$process p, v$session s
where s.paddr = p.addr(+)
and s.sid = (select sys_context(‘USERENV’,’SID’) from dual)
order by 1;
================================================
Find details of an other session using its SID:
=================================================
set linesize 132
set pagesize 1000
set echo on
col rbs format a6
col oracle_user format a12 heading “Oracle|Username”
col sid format 99999 heading “Oracle|SID”
col serial# format 99999 heading “Oracle|Serial#”
col unix_pid format a6 Heading “Unix|PID”
col Client_User format a12 Heading “Client|Username”
col Unix_user format a12 Heading “Unix|Username”
col Logon_time format a11 Heading “Login|Time”
col lock_wait format a4 heading “Lock|Wait”
select
s.username oracle_user,
s.osuser client_user,
p.username unix_user,
s.status status,
s.sid sid,
s.serial# serial#,
p.spid unix_pid,
to_char(s.logon_time,’MM-DD HH24:MI’) logon_time,
decode(s.lockwait,NULL,’No’,’Yes’) lock_wait
from v$process p, v$session s
where s.paddr = p.addr(+)
and s.sid = &sid
order by 1;
SYS@CLOUD12C:SQL> oradebug setospid 19925
Oracle pid: 53, Unix process pid: 19925, image: oracle@ip-172-31-30-62 (TNS V1-V3)
SYS@CLOUD12C:SQL> — set the trace file size to unlimitd
SYS@CLOUD12C:SQL> oradebug unlimit
Statement processed.
SYS@CLOUD12C:SQL> — now turn on tracing
SYS@CLOUD12C:SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SYS@CLOUD12C:SQL> — To check the trace file name that is being generated for your tracing session
SYS@CLOUD12C:SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/cloud12c/cloud12c/trace/cloud12c_ora_19925.trc
SYS@CLOUD12C:SQL> — Turn off the tracing once done
SYS@CLOUD12C:SQL> oradebug event 10046 trace name context off
Statement processed.
Loading...
