How to resolve user locking problems

Method 1: Check and remove session locking other from Database

Find the USER locking others
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

Output:

USER SERIAL# SID USERNAME ID1 SQL_TEXT
13 23 ALIMA 393242 update authuser.emp set salary=1000

To Kill session
SQL>alter system kill session ’23,13’;
Session Killed.
Method 2: Check and remove by process id from Unix session, this query give you more details about the locking problem

To Kill session

On the Unix box
$ps -ef|grep username (i.e. alima)

alima 12102 12083 0 10:55:14 pts/3 20:98 sqlplus
alima 13011 12078 0 9:25:01 pts/5 0.12 -ksh

Process id is the first number column, determine which process id by matching the number

i.e. the process id to be killed is 12102
$kill -15 12102
$ps -ef|grep 12102

If the process id do not die
$kill -9 12102

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s