Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sudhanshu.

Asked: August 30, 2017 - 9:24 am UTC

Last updated: August 30, 2017 - 1:55 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Team,

I have a table with approx 30 million records, where I need to modify the column length of 3 column from Varchar2(200) to Varchar2(400). My doubt is in case there if any insert is happening on the table at the moment I am altering the column length, will it create any lock or issue or data loss.

Regards,
Sudhanshu

and Chris said...

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/

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.