It is sometimes interesting to retrieve running sql statement when a program hangs and process is pending.
Principle is to :
- retrieve process id
=========================================================
-> 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;
=========================================================
- connect with system account
- execute command below :
oradebug setospid <process id>
- activate tracing by :
oradebug event 10046 trace name context forever, level 12
- deactivate tracing by :
oradebug event 10046 trace name context off