RE: dml that made locking
- From: "Tony Aponte" <Tony_Aponte@xxxxxxxxx>
- To: <ujang.jaenudin@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2007 12:35:48 -0400
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@xxxxxxx <
/tmp/DanglingLockFrom_&1..lst
EXIT;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
