Quick way to trace a session on a server

If you identify an Oracle database process consuming a high amount of CPU, or want to trace it for any reason, you can simply run the following script and pass the PID of the offending process when requested.

begin
  for cur in (select sid,serial# from v$session where paddr in (select addr from v$process where spid = &spid)) loop
    dbms_monitor.session_trace_enable(cur.sid,cur.serial#,true,true);
  end loop;
end;
/
exit

You could also create an alias for the script, something like…

alias trcspid='sqlplus / as sysdba @script_location.sql'

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.