Re: Online redef - is there a better way?

"Ind-dba" <oraclearora_at_googlemail.com> wrote in message news:13dfbc97-f396-4c3a-92c9-254623a19bfa_at_n33g2000pri.googlegroups.com...
> 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html