PROCESS HUNG

[mary]

Hi all,
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?
Thank you.
p.s. the quick solution is to kill those sessions but I though I may nee to do some investigation before.

[sreddy]

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

spool currun.lis

break on sid skip 1 on username on osuser on status

SELECT
substr(SID,1,5) SID,
substr(V$SESSION.USERNAME,1,15) USERNAME,
SUBSTR(OSUSER,1,8) OSUSER,
STATUS,
SQL_TEXT
FROM
V$SESSION,
V$PROCESS,
V$SQLTEXT
WHERE
V$SESSION.PADDR = V$PROCESS.ADDR
AND V$SESSION.SQL_HASH_VALUE=V$SQLTEXT.HASH_VALUE
AND V$SESSION.SQL_ADDRESS=V$SQLTEXT.ADDRESS
AND TYPE = 'USER'
AND V$SESSION.USERNAME Is Not NULL
AND STATUS IN('ACTIVE','KILLED')
ORDER BY
sid,
piece;

spool off;
set lines 80;