Friday, July 25, 2008

Enable SQL trace for remote session

If we have the Remote client's Process ID provided by the users, running the SQL given below (copy and paste inside TOAD sql editor and execute) will provide the statements to be run for enabling/disabling session trace. Also the trace file name with full path will be provided.


select 'Level 1 Trace' "For Trace Level",'execute sys.DBMS_SYSTEM.Set_Sql_Trace_In_Session(' || s.sid || ', ' || s.serial# || ', TRUE)' "Command for Starting Trace" ,
'execute sys.DBMS_SYSTEM.Set_Sql_Trace_In_Session(' || s.sid || ', ' || s.serial# || ', FALSE)' "Command for Stopping Trace", 
s.program, s.username , c.value || '/' || lower(d.value)|| '_ora_' ||p.spid||'.trc' "TRACE FILE"  
from 
V$SESSION s, V$PROCESS p,v$parameter c, v$parameter d  where 
p.addr=s.paddr and s.process=:RemoteClientProcessID and
c.name = 'user_dump_dest' and d.name = 'db_name'
union
select 'Level 1 Trace (another stmt...)' "For Trace Level",'execute sys.DBMS_MONITOR.session_trace_enable(' || s.sid || ', ' || s.serial# || ', waits=>FALSE, binds=>FALSE)' "Command for Starting Trace" ,
'execute sys.DBMS_MONITOR.session_trace_disable(' || s.sid || ', ' || s.serial# || ')' "Command for Stopping Trace", 
s.program, s.username ,
c.value || '/' || lower(d.value)|| '_ora_' ||p.spid||'.trc' "TRACE FILE"  
from 
V$SESSION s, V$PROCESS p,v$parameter c, v$parameter d  where 
p.addr=s.paddr and s.process=:RemoteClientProcessID and
c.name = 'user_dump_dest' and d.name = 'db_name'
union
select 'Level 4 ; with Bind values' "For Trace Level",'execute sys.DBMS_MONITOR.session_trace_enable(' || s.sid || ', ' || s.serial# || ', waits=>FALSE, binds=>TRUE)' "Command for Starting Trace" ,
'execute sys.DBMS_MONITOR.session_trace_disable(' || s.sid || ', ' || s.serial# || ')' "Command for Stopping Trace", 
s.program, s.username ,
c.value || '/' || lower(d.value)|| '_ora_' ||p.spid||'.trc' "TRACE FILE"  
from 
V$SESSION s, V$PROCESS p,v$parameter c, v$parameter d  where 
p.addr=s.paddr and s.process=:RemoteClientProcessID and
c.name = 'user_dump_dest' and d.name = 'db_name'
union
select 'Level 8 ; with bind and wait values' "For Trace Level",'execute sys.DBMS_MONITOR.session_trace_enable(' || s.sid || ', ' || s.serial# || ', waits=>TRUE, binds=>TRUE)' "Command for Starting Trace" ,
'execute sys.DBMS_MONITOR.session_trace_disable(' || s.sid || ', ' || s.serial# || ')' "Command for Stopping Trace", 
s.program, s.username ,
c.value || '/' || lower(d.value)|| '_ora_' ||p.spid||'.trc' "TRACE FILE"  
from 
V$SESSION s, V$PROCESS p,v$parameter c, v$parameter d  where 
p.addr=s.paddr and s.process=:RemoteClientProcessID and
c.name = 'user_dump_dest' and d.name = 'db_name';



Pre-requisites:
1) SELECT privilege on V$SESSION, V$PROCESS views (required for running this sql)
2) EXECUTE privilege on DBMS_SYSTEM or DBMS_MONITOR package.
3) Write permission to the directory where we want to store the trace output file ie. udump
Attn.: This needs user to connected from some Unix client (App Server / computer) to oracle DB Server (Unix)
For Windows based client computers, the client Process id is a combination of Process:Thread