Reputation: 77
I'm using oracle11g database. I have a table in the name of phonenumbers_tbl
and I performed the DROP
command on that table. But It is returning the error resource busy and acquire with NOWAIT specified or timeout expired
. After that I was alter the session with the command alter session set ddl_lock_timeout = 600
and Again try to drop the table. But still This error is persisting again
Upvotes: 3
Views: 35211
Reputation: 748
You can execute the follow code:
select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status
from gv$locked_object a , gv$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
Upvotes: 0
Reputation: 49
At first get table lock session then kill session
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
ALTER SYSTEM KILL SESSION 'sid,serial#' ;
Upvotes: 1
Reputation: 77
yes ! finally I got a solution that is moved the table phonenumber_tbl
to another tablespace system
and dropped the table.
Upvotes: 1
Reputation: 6346
Try to execute this first ,and check whether anyone from other session
or your session
put a lock on that table .If you
have put a lock on that table,try to do commit/rollback
.If someone else
put a lock ,ask him/her
or if you have rights kill his session
.And then drop the table.
select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
Upvotes: 3