Re: Online redef - is there a better way?

From: Jonathan Lewis <>
Date: Mon, 16 Feb 2009 08:04:17 -0000
Message-ID: <>

"Ind-dba" <> wrote in message
> Thanks Jonathan for your reply!
> While doing FINISH redef -- i saw wait event "Wait for Table Lock" --
> does that means that oracle will wait for table lock? And if we wait
> -- we can avoid ORA-54? what do you say ?
> 1* select username,event,b.object_name from v$session, dba_objects b
> where username='SYSTEM' and ROW_WAIT_OBJ#=b.object_id;
> SQL> /

You didn't say which session was waiting for a table lock, the one doing the redefinition, or one of the sessions doing inserts.

Why not try the experiment -
  session 1 - insert rows into table, don't commit   session 2 - enable sql_trace, try to start redefinition -- does it get stuck
  session 3 - insert rows into table, don't commit -- does it get stuck   session 1 - commit -- is session 2 still stuck, or has the trace moved on, does it get stuck again
  session 1 - insert more rows - don't commit

and so on ...

If your "finishing" session is waiting for a lock, are other sessions stuck behind it waiting, or do they fail. Does the "finishing" session deliberately timeout and reacquire its lock so that any sessions waiting behind it can clear before it tries again ?

I can't tell you whether your application is going to get unexpected errors, because I don't know how your application is going to respond to a blocking lock from "finishing" session.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Mon Feb 16 2009 - 02:04:17 CST

Original text of this message