RE: Move table online and update the indexes at the same time.

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: