So you've got a table like this:
create table t (
x varchar2(10)
);
And you want to increase the length of X from 10 -> 20. But you have an uncommitted insert in another session:
insert into t values ('test');
So when you come to alter the table in a second session, this happens:
alter table t modify (x varchar2(20));
Error starting at line : 1 in command -
alter table t modify (x varchar2(20))
Error report -
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Hmmm. The insert is blocking the alter.
But what's this timeout business?
11g brought a new DDL_LOCK_TIMEOUT parameter. This allows you to state a number of seconds before throwing ORA-54.
So let's do that:
alter session set ddl_lock_timeout=600;
alter table t modify (x varchar2(20));
and the alter table sits there, waiting ten minutes for the other transaction to finish.
But while you're waiting, someone else comes along and tries to insert:
insert into t values ('test');
And now they're stuck too! The alter table needs an exclusive lock on T. Which is blocking the third insert.
So we've got a chain of blocking sessions:
with w as (
select
chain_id,rownum n,level l
,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' "session"
,lpad(' ',level,' ')||w.wait_event_text ||
case
when w.wait_event_text like 'enq: TM%' then
' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive')
||( select ' on '||object_type||' "'||owner||'"."'||object_name||'" ' from all_objects where object_id=w.p2 )
when w.wait_event_text like 'enq: TX%' then
(
select ' on '||object_type||' "'||owner||'"."'||object_name||'" on rowid '
||dbms_rowid.rowid_create(1,data_object_id,relative_fno,w.row_wait_block#,w.row_wait_row#)
from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id
)
end "wait event"
, round(w.in_wait_secs/60) "minutes"
from v$wait_chains w
join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance)
connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance
start with w.blocker_sid is null
)
select * from w
where chain_id in (
select chain_id from w
group by chain_id
having max("minutes") >= 0.1 and max(l)>1
)
order by n;
CHAIN_ID N L session wait event minutes
1 6 1 db12201 '125,55208@1' SQL*Net message from client 7
1 7 2 db12201 '124,21544@1' enq: TM - contention mode eXclusive on TABLE "CHRIS"."T" 2
1 8 3 db12201 '126,10850@1' enq: TM - contention mode Row-X on TABLE "CHRIS"."T" 2
The second will remain blocked until it hits the timeout or the first commits. Same for the third session.
So to let everyone continue, you need to go back to the first session and run:
commit;
HT to Franck Pachot for the v$wait_chains query:
https://blog.dbi-services.com/oracle-locks-identifiying-blocking-sessions/