How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus
How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus
select * from V$SESSION
where OSUSER like ‘c_sgoud’ — User name of the terminal ( may be your windows login name )
Alter system kill session ‘146,46619’
select sid, serial# from v$session where username = ‘USER’;
alter system kill session ‘SID,SERIAL#’;
you need to find first lock
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;
by this query you can find lock
then you can kill
col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
— b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
and b.status=’ACTIVE’
–and b.module like ‘%blbn%’
–and b.username=’ENBAPP23′
order by program ,OS_USER
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
— b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
and b.status=’ACTIVE’
–and b.module like ‘%blbn%’
–and b.username=’ENBAPP23′
order by program ,OS_USER
How to Unlock the Objects
The following query could be useful :
Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))
Get the Process id from the query. Login as unix user and run the following command to kill the process.
Leave a Reply
Want to join the discussion?Feel free to contribute!