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
;

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

 

Kindle Wireless Reading Device (6" Display, Global Wireless, Latest Generation)



 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.

 Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)