I kicked some procedures off 5 hours ago. Normally they wouldn't take more than 45 min to run. It is still running and my sqlplus session screen is blank.
How do I check what is going on? How to find out why they hung?
p.s. the quick solution is to kill those sessions but I though I may nee to do some investigation before.
See this gets something for you. It gives SQL its executing, with which you can figure out what are your procs that are pending...
This script reports on all ACTIVE & KILLED sessions currently retaining a connection to the database instance. Very useful script for determining which user sessions are currently ACTIVE or have not resolved in KILLED state.
set lines 120
break on sid skip 1 on username on osuser on status
V$SESSION.PADDR = V$PROCESS.ADDR
AND TYPE = 'USER'
AND V$SESSION.USERNAME Is Not NULL
AND STATUS IN('ACTIVE','KILLED')
set lines 80;
Click Here to Expand Forum to Full Width