Hello all,
i am working in 9i Oracle RDBMS on AIX IBM OS
most of the time we face locking issue and there comes more then different 50 sessions which locking different tables.
here is my script to find out what sessions are locking what tables.
set lines 200
set pages 50000
col SID for 99999
col SERIAL# for 999999
col username format a12
col sql format a55
col event format a50
col program format a40
col MINS for 9999
col ID1 for 999999
col object_name for a35
col locktype for a8;
select /*+ ORDERED */ s.sid,s.serial#,s.status,round(s.last_call_et/60) MINS,s.program,l.ID1,d.object_name,d.object_type,l.type as locktype
from v$lock l,v$session s, dba_objects d
where l.sid = s.sid
and l.ID1 = d.object_id
and s.username = 'MULDMS'
and last_call_et > 120 and l.type <> 'TX' order by s.status,MINS,l.ID1,l.type;
and the out put it gives something like
SID SERIAL# STATUS MINS PROGRAM ID1 OBJECT_NAME OBJECT_TYPE LOCKTY
----- ------- -------- ----- ---------------------------------------- ------- ----------------------------------- ------------------ ------
8981 1069 ACTIVE 2 frmweb@Appsdms31 (TNS V1-V3) 30407 VH_BTN TABLE TM
1642 512 ACTIVE 15 frmweb@Appsdms16.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
690 1057 ACTIVE 17 frmweb@appsdms27.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
699 1787 ACTIVE 17 frmweb@appsdms28.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
690 1057 ACTIVE 17 frmweb@appsdms27.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
690 1057 ACTIVE 17 frmweb@appsdms27.dil.com (TNS V1-V3) 30643 VT_CHECK_INOUT TABLE TM
10877 895 ACTIVE 18 frmweb@Appsdms2.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
6654 1085 ACTIVE 18 frmweb@Appsdms11.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
1912 1655 ACTIVE 18 frmweb@Appsdms14.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
10877 895 ACTIVE 18 frmweb@Appsdms2.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
6654 1085 ACTIVE 18 frmweb@Appsdms11.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
1912 1655 ACTIVE 18 frmweb@Appsdms14.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
1754 1582 ACTIVE 18 frmweb@Appsdms15.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
10877 895 ACTIVE 18 frmweb@Appsdms2.dil.com (TNS V1-V3) 30643 VT_CHECK_INOUT TABLE TM
6654 1085 ACTIVE 18 frmweb@Appsdms11.dil.com (TNS V1-V3) 30643 VT_CHECK_INOUT TABLE TM
10708 982 ACTIVE 19 frmweb@appsdms27.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
5428 855 ACTIVE 19 frmweb@appsdms27.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
10708 982 ACTIVE 19 frmweb@appsdms27.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
5428 855 ACTIVE 19 frmweb@appsdms27.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
10278 897 ACTIVE 20 frmweb@Appsdms8.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
6261 1056 ACTIVE 20 frmweb@appsdms12 (TNS V1-V3) 28846 AM_DOCS TABLE TM
6598 1413 ACTIVE 20 frmweb@Appsdms8.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
1312 1000 ACTIVE 20 frmweb@Appsdms15.dil.com (TNS V1-V3) 28846 AM_DOCS TABLE TM
10278 897 ACTIVE 20 frmweb@Appsdms8.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
6261 1056 ACTIVE 20 frmweb@appsdms12 (TNS V1-V3) 30407 VH_BTN TABLE TM
1312 1000 ACTIVE 20 frmweb@Appsdms15.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
3521 717 ACTIVE 20 frmweb@Appsdms16.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
6598 1413 ACTIVE 20 frmweb@Appsdms8.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
6598 1413 ACTIVE 20 frmweb@Appsdms8.dil.com (TNS V1-V3) 30643 VT_CHECK_INOUT TABLE TM
1312 1000 ACTIVE 20 frmweb@Appsdms15.dil.com (TNS V1-V3) 30643 VT_CHECK_INOUT TABLE TM
9131 1002 INACTIVE 2 frmweb@appsdms29.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
4887 1040 INACTIVE 2 frmweb@Appsdms2.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
151 1982 INACTIVE 2 frmweb@Appsdms1.dil.com (TNS V1-V3) 80224 RD_ENQEXD TABLE TM
846 988 INACTIVE 3 frmweb@Appsdms15.dil.com (TNS V1-V3) 29228 GM_VIN TABLE TM
5364 1149 INACTIVE 3 frmweb@appsdms28.dil.com (TNS V1-V3) 30407 VH_BTN TABLE TM
9934 789 INACTIVE 3 frmweb@Appsdms2.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
7511 1092 INACTIVE 3 frmweb@appsdms27.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
3595 1104 INACTIVE 3 frmweb@Appsdms14.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
846 988 INACTIVE 3 frmweb@Appsdms15.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
945 670 INACTIVE 3 frmweb@appsdms28.dil.com (TNS V1-V3) 30455 VH_PSF TABLE TM
and then i have to kill the session one by one . thaat is a tedious task to do.
i want to modify this script which will give me all the
kill statement and i just have to run it
can someone help me in this regard as i am weak in sql/plsql
thanks and many thanks