Runaway Sql Sessions

Sometime you are running a query using toad or sqlDeveloper and you decide to kill toad or sqlDeveloper session, query still keep on executing. it may cause problem because tables rows may be locked and you may not do any further processing. if you know your runaway query then finding session is easy. The query given below can give you session and serial# and then you may use alter system to kill your runaway session.

 SELECT vs.sid,
       vs.serial#,
       vs.username,
       optimizer_mode,
       hash_value,
       address,
       sql_fulltext 
       /* sql_fulltext column is available in 10g. for 9i, you may use sql_text. 
          sql_text column gives only 1000 characters of the query. 
          sql_fulltext probably gives full query. i have not verified it.  
       */
  FROM v$sqlarea                    sa  
     , v$process                    vp    
     , v$session                    vs
  WHERE vs.paddr               = vp.addr
    AND vs.sql_hash_value      = sa.hash_value
    AND vs.sql_address         = sa.address
    AND vs.username IS NOT NULL
    AND sql_text LIKE 'INSERT INTO MYTABLE%' 
                      /* change above line with starting part of you query 
                      */

ALTER SYSTEM KILL SESSION '<sid>,<session#>'
.
.
Suppose a package is being locked then then you can get SID, SERIAL# using following query and then kill the session

SELECT ses.* 
  FROM v$access acc
     , v$session ses
 WHERE acc.object = 'VVAR_PCI_COMPLIANCE_PKG' /* change to your package that is locked */
   AND acc.sid    = ses.sid;