Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 19 June 2012

Releasing Exclusive Locks and Killing Sessions from Oracle

This is useful for solving resource busy issues and killing unwanted background process made by exclusive lock.

1. Connect as sysdba and then run this query
select a.sid,serial#,process,program,terminal from v$lock a,v$session b where a.lmode=6 and a.sid > 10 and a.sid=b.sid;

2. Now use the alter command to kill the session by putting the SID and Serial# getting from the above query.
For Example:
Alter system kill session '448,42179';

3. Again using the same query check the correctness of Alter system command
select a.sid,serial#,process,program,terminal from v$lock a,v$session b where a.lmode=6 and a.sid > 10 and a.sid=b.sid;
 
Find those object of table which is locked
.
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

Find the session which are holding the lock:
select type, id1, id2, lmode, request
from v$lock
where sid = (select sid from v$mystat where rownum = 1);

Killing that sessions holding the lock.
Select SERIAL# from v$session where SID=448;
SERIAL#
----------
42179
Alter system kill session '448,42179';
System altered.

Note: Usually the locks are released when the DML statements are rollback or committed.

More details on Oracle locks, Follow the link: http://shahiddba.blogspot.com/2012/04/find-whose-lock-is-blocking.html

0 comments:

Post a Comment