I've got couple Metalink notes on dbms_redefinition package. They explain limitations and there is also some info in the docs as well. If someone is interested - 177407.1 and 149564.1. To change storage parameters for new blocks it's enough to change it right away, but obviously we need to go further. ASSM has several nasty bugs with LOBs and that's what we've got. However, can you elaborate on ASSM not suitable for heavy load tables? Sorry for possible confusion, but saying "wrong tablespace" I didn't mean just fancy naming. It's IO distribution problem. Regards, Alex From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>@freelists.org on 27-07-2004 02:04 EST Please respond to oracle-l@xxxxxxxxxxxxx Sent by: oracle-l-bounce@xxxxxxxxxxxxx To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx> cc: Subject: RE: Move table online and update the indexes at the same time. Alex, do you have table monitoring on? That is, do you know exactly what's happening to these tables? You'd need lock at end when you're doing rename. You can change the storage clause for new blocks at least. If this is heavy hitter, as you say, you may not want ASSM. Not sure why tables are in wrong tablespace, but I'd find something more productive to do if that's only reason. Larry -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Alexandre Gorbatchev Sent: Tuesday, July 27, 2004 12:49 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Move table online and update the indexes at the same time. We would definitely test it properly before doing in production but just wanted to know where the possible problems are. By the way, when is the lock required - in the beginning or in the end? I move the table for several reasons: 1.) CREATE TABLE ... AS SELECT produces the table which is much smaller (sometimes 2-3 times). We are trying to reduce the space usage with it. So it seems the tables are space. 2.) Change storage clause - FREELIST, FREELIST GROUP or move to ASSM (not sure, because it seems there are several bugs that we might hit in our environment) 3.) Physical layout reorganization. Some tables are in wrong tablespaces. Thanks for your help. Alex From: Tanel Poder <tanel.poder.003@xxxxxxx>@freelists.org on 27-07-2004 01:36 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. > 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. I've never used it in production myself, but as much I've heard, it does have its problems and limitations - thus extensive testing would be needed. It will increase your load, since it basically creates a materialized view log for your table (and you have to clone the table manually + build the indexes + apply the changes incrementally later on). Redefinition package will require a lock for short time on your table if used properly. The temporary locking could be implemented by deferring your transactions for a short time if possible and/or having a tight pl/sql loop which retries the operation until it succeeds. Btw, in 10g there is a "ddl_wait_for_locks" parameter which you can set to true on your session level - that way Oracle will execute DDL commands in wait mode, thus waiting until all incompatible locks on given object are released, instead of erroring out immediately. Btw, why are you trying to move your tables? 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 ----------------------------------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- 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 -----------------------------------------------------------------