Pete, It does work for IOT and I guess it wouldn't even invalidate the indexes on IOT because they are based on Primary key. Maybe only one more statement with UPDATE REFERENCES if it's not in MOVE clause as an option. Just don't remember out of my head. A short lock might be a problem - that's because some of "online" operation never worked online for our environment - there is just no chance to get the lock even looping like a crazy. Regards, Alex From: Pete Sharman <peter.sharman@xxxxxxxxxx>@freelists.org on 27-07-2004 08:29 ZE10 Please respond to oracle-l@xxxxxxxxxxxxx Sent by: oracle-l-bounce@xxxxxxxxxxxxx To: oracle-l@xxxxxxxxxxxxx cc: Peter Ross Sharman <PETER.SHARMAN@xxxxxxxxxx> Subject: RE: Move table online and update the indexes at the same time. Alex DBMS_REDEFINITION does need an exclusive table lock for a short duration wh= en it updates the data dictionary. = BTW, in your earlier message you referred to ALTER TABLE MOVE. That's for = IOT's only and wouldn't help anyway. = Pete = "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook = "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] = On Behalf Of Alexandre Gorbatchev Sent: Tuesday, 27 July 2004 8:05 AM To: oracle-l@xxxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Move table online and update the indexes at the same time. Tanel, Thanks for the reply. It's 9i. I thought about table redefinition but I am not sure how it = affect the performance. Second, I wonder if there a need to get a lock on = the table just for a split of a second. We do not have any chance locking = the table since there are always several transactions using it. What is your experience? Since it appeared only in 9i I am a bit worried = about using it. Thanks, Alex From: Tanel Puder <tanel.poder.003@xxxxxxx>@freelists.org on 27-07-2004 = = 00:34 ZE3 Please respond to oracle-l@xxxxxxxxxxxxx Sent by: oracle-l-bounce@xxxxxxxxxxxxx To: <oracle-l@xxxxxxxxxxxxx> cc: Subject: Re: Move table online and update the indexes at the same time. > Hi all, > We need to rebuild our table and index segments. I guess we wouldn't = have > problems rebuilding the indexes online. However, I don't see any way to > move tables online. Of course, I can issue ALTER TABLE ... MOVE ONLINE, > but this would invalidate all indexes of the table. I will have to = rebuild Oracle 10g: alter table shrink space cascade Oracle 9i: dbms_redefinition package Pre-Oracle 9i: custom mechanism, cloning table and recording changes with triggers or snapshot logs. Tanel. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------