INTRODUCTION
Sometimes when multiple users perform operations like update/delete on a table, there is a chance for a table lock.
ISSUE
SQL table is locked.
SOLUTION
To unlock all the locked tables, need to login as an ADMIN user.
Step 1
Check if the Package or table are locked using the below query
SELECT b.object_name, a.session_id, a.oracle_username, a.os_user_name, a.process, a.locked_mode
FROM v$locked_object a, all_objects b
WHERE a.object_id = b.object_id;
|
Step 2
Get the serial number for the session based on the session id got from above query.
SELECT SID, serial#, ownerid, status, server, username, osuser, process, machine
FROM v$session
WHERE SID = 'Session id from above query';
|
Step 3
Kill the session below command.
ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2';
Example:
1) SELECT SID, serial# FROM v$session WHERE SID in (select session_id from v$locked_object);
2) ALTER SYSTEM KILL SESSION '8,19087';
|
ALTERNATE SOLUTION
We can also unlock the table by below approach
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
--To check
SELECT object_id FROM dba_objects WHERE object_name=’MYOBJECT';
SELECT sid FROM v$lock WHERE id1=360254;
ALTER SYSTEM KILL SESSION '3434,92193' ;
|
No comments :
Post a Comment