RE: dml that made locking

I used this SQLPlus script to kill sessions holding locks more than N minutes. It accepts a username and minute integer as parameters. It first indetifies the sessions for the username parameter that have an open transaction age greater than the minutes parameter. It then collects session and locked object information into a spool file and proceeds to kill the session. It mails out an alert with the details after the session is marked for kill. I never needed to know the actual SQL that locked the object so it's not in the script. Maybe you can look at the object that's locked by the session, the cursors in v$open_cursor and some searches on the sql_text.

Regards,
Tony Aponte

SET FEEDBACK OFF VERIFY OFF TAB OFF TRIMOUT ON LINESIZE 132 PAGESIZE 0 HEAD OFF

--

SPOOL /tmp/DanglingLockFrom_&1..lst;

-- 

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

CURSOR opentxn_cur IS

SELECT 

  sysdate,

  sid,

  serial#,

  username,

  substr(terminal,1,20) terminal,

  osuser,

  s.logon_time,

  t.start_time,

  round((sysdate - TO_DATE( start_time,'MM/DD/YY HH24:MI:SS'))*24*60,0)
minutes_pending,

  s.status,

  s.last_call_et

FROM 

  v$transaction t,

  v$session s

WHERE 

  t.ses_addr = s.saddr and

  s.username = '&1.' and

  s.status = 'INACTIVE' and  

  round((sysdate - TO_DATE( start_time,'MM/DD/YY HH24:MI:SS'))*24*60,0)
> &2  

ORDER BY 

  t.start_time;

 

  l_sid V$SESSION.sid%TYPE;

 

  CURSOR locked_obj_cur IS

  select o.object_name, l.oracle_username, l.object_id, l.process,
l.locked_mode

  from v$locked_object l

  ,    obj o

  where l.session_id=l_sid

  and l.object_id = o.object_id;

 

  CURSOR blocked_sess_cur IS

  select sid, id1, id2, lmode, request

  from v$lock

  where sid <> l_sid

  and id1 in (select id1 from v$lock where sid=l_sid and block=1);

 

  l_username v$SESSION.USERNAME%TYPE;

 

BEGIN

  FOR temp_cur IN opentxn_cur

  LOOP

 

      l_sid:=temp_cur.sid;

 

          DBMS_OUTPUT.PUT_LINE('--');

          DBMS_OUTPUT.PUT_LINE(RPAD('SID/SERIAL#:',25,' ')


||temp_cur.SID||'/'||temp_cur.SERIAL#);


          DBMS_OUTPUT.PUT_LINE(RPAD('Oracle User Name:',25,'
')||temp_cur.USERNAME);

          DBMS_OUTPUT.PUT_LINE(RPAD('Server Name:',25,' ')


||temp_cur.terminal);


          DBMS_OUTPUT.PUT_LINE(RPAD('O/S User Name:',25,' ')


||temp_cur.osuser);


          DBMS_OUTPUT.PUT_LINE(RPAD('Logon Time:',25,' ')


||TO_CHAR(temp_cur.logon_time,'MM/DD/YY HH24:MI:SS'));


          DBMS_OUTPUT.PUT_LINE(RPAD('Xtn Time:',25,' ')


||temp_cur.start_time);


          DBMS_OUTPUT.PUT_LINE(RPAD('Minutes Pending:',25,' ')


||temp_cur.minutes_pending);


          DBMS_OUTPUT.PUT_LINE(RPAD('Session Status:',25,' ')


||temp_cur.status);


          DBMS_OUTPUT.PUT_LINE(RPAD('Seconds In Above Status:',25,'
')||temp_cur.last_call_et);

 

      DBMS_OUTPUT.PUT_LINE('Locked Objects: ');

      FOR locked_obj_rec in locked_obj_cur LOOP

        DBMS_OUTPUT.PUT_LINE(RPAD(LPAD('Name:',5,' '),25,'
')||locked_obj_rec.object_name||'('||locked_obj_rec.object_id||')');

      END LOOP;

 

      DBMS_OUTPUT.PUT_LINE('Blocked Sessions: ');

      FOR blocked_sess_rec in blocked_sess_cur LOOP

 

         select username

         into l_username

         from v$session

         where sid=l_sid;

 

         DBMS_OUTPUT.PUT_LINE(RPAD(LPAD('User:',5,' '),25,'
')||blocked_sess_rec.sid||' - '||l_username||'
('||blocked_sess_rec.id1||'/'||blocked_sess_rec.id2||')');

      END LOOP;

 

          EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
temp_cur.SID || ',' || temp_cur.Serial# || '''';

          DBMS_OUTPUT.PUT_LINE('Disposition:             Killed');

  END LOOP;

END;

/

 

SPOOL OFF;

HOST mailx -s "OEM9i:: Dangling Locks Detector" dbaalerts_at_hsn.net <
/tmp/DanglingLockFrom_&1..lst

EXIT;
--
http://www.freelists.org/webpage/oracle-l