You could need to trace a particular session for example if issue occurs for only one user or if you can't shutdown database.
Principle is to :
- Retrieve number from a current session of Oracle Applications,
the easiest way is to :
- Connect to System Administrator responsibility
- Menu Security => User => Monitor
- Note the Oracle Process (PID in fact) for the proper User Name
- Connect to sqlplus (system account)
- Run this script to get session and serial numbers :
select p.pid, p.spid, s.sid, s.serial#, s.username
from v$process p , v$session s
where p.addr = s.paddr and p.pid = &your_pid;
=> enter PID found before it will give you needed session and serial numbers
- now run this command to activate trace for your session :
execute dbms_system.set_sql_trace_in_session(<SID>, <SERIAL#>, TRUE)