Find session info
select username, status, sid, serial#
from v$session
where username ='USER_NAME
Enable tracing for other session
EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
or
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
- Level 0 - No trace. Just like switching sql_trace off.
- Level 2 - The equivalent of regular sql_trace.
- Level 4 - The same as level 2, but with the addition of bind variable -
values. - Level 8 - The same as level 2, but with the addition of wait events.
- Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.
or
exec dbms_system.set_sql_trace_in_session(3,5023,true);
Find dump file
Trace file named with spid under user_dump_dest location
-- To find SPID
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/
eg: instancename_ora_spid.trc i.e orcl_ora_4393.trc
show parameter user_dump_dest
cd user_dump_dest location
ls -ltr *ora_spid*.trc
eg: ls -ltr ora_4393.trc
Show dump log file
tkprof one_big.trc output=one_big.txt sys=no
网友评论