Cause of the Problem:
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.
Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it-
-Re run the DDL at a later time when the database become idle.
-or, Kill the sessions that are preventing the exclusive lock.
-or, Prevent end user to connect to the database and then run the DDL.
Kill the sessions:
set long 30000
set lines 200 pages 200
col username format a20
col sess_id format a20
col mode_held format a20
col object format a30
SELECT oracle_username || ' (' || s.osuser || ')' username ,(s.sid || ',' || s.serial#) as sess_id
,s.STATUS "Session status"
,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 username, session_id;
SQL> alter system kill session '142, 232';
After killing all the locked sessions, I connected to that schema where the row lock arise and press commit.
set lines 200 pages 200
col ses_serial format a30
Also have to see-
SELECT a.sid||','||a.serial# ses_serial,a.username,a.osuser
,to_char(a.logon_time, 'hh24:mi dd/mm/yy') login_time
FROM v$session a,v$process b
WHERE a.username IS NOT NULL AND a.paddr=b.addr AND a.type='USER'
AND a.logon_time> = sysdate- 1/48 --last 30 min
Order by a.sid,a.username;
Kill from OS level
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr and vs.sid=319
order by vs.sid;
SID ---------- USERNAME ----------OSUSER ----------FG_PID ----------BG_PID
---------- ------------------------------ ------------------------------ ------------------ -----------------------------
319 -------------------------------------------oracle -------------17314 --------------17314
bash-3.00$ kill -9 17314
