PROCESS HUNG
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PROCESS HUNG

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    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.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width