A common error we face on day to
day life. Happens when someone tries to modify a table which is already locked
by other user.
Now there are couple of ways to
get rid of it.
1. Check to see which user is locking the object and then
kill the session.
select a.sid, a.serial#
from v$session a, v$locked_object b,
dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';
alter system kill session
'sid,serial#';
2. In 11g we can set ddl_lock_timeout to allow DDL to wait for the object becomes available. We can specify how long we’d want to wait for executing DDL.
SQL> alter session set
ddl_lock_timeout = 600;
Session altered.
SQL> alter table emp add (gender
varchar2(10));
Table altered.
3. If you don’t want to hamper other users, then either
try executing DDL at off-peak hours or
4. Wait for few minutes until the other user releases the
lock on same object.
5. In 11g you can put a table in readonly mode to ensure
no one locking it and then execute your command.
SQL> alter table emp read only;
Session altered.
Table altered.
