http://arjudba.blogspot.kr/2009/01/ora-00054-resource-busy-and-acquire.html
LOCK이 걸려서 테이블의 컬럼 추가 및 삭제 등의 작업을 진행할수 없을 경우
In order to see locked object query,
SQL> set linesize 130
SQL> set pages 100
SQL> col username format a20
SQL> col sess_id format a10
SQL> col object format a25
SQL> col mode_held format a10
SQL> select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
USERNAME SESS_ID OBJECT OBJECT_TYPE STATUS MODE_HELD
-------------------- ---------- ------------------------- ------------------- ------------ ----------
MAXIMSG (A) 142,232 MAXIMSG.A TABLE Not Blocking Row-X (SX)
OMS (DBA2\ershad) 143,280 OMS.T TABLE Not Blocking Row-X (SX)
OMS (DBA2\ershad) 143,280 OMS.T1 TABLE Not Blocking Row-X (SX)
Here we see there is 3 types of locking. In our case the object is A. And we see the sid=142 and serial#=232 is preventing us from locking the table in exclusive mode. So to achieve our task we can kill the session as below.
SQL> alter system kill session '142, 232';
System altered.
