From oracle-l-bounce@freelists.org Mon Jul 26 17:33:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6QMXMQ10533 for ; Mon, 26 Jul 2004 17:33:32 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6QMXC610449 for ; Mon, 26 Jul 2004 17:33:22 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A6DA272E238; Mon, 26 Jul 2004 17:11:42 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31684-58; Mon, 26 Jul 2004 17:11:42 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C90E372DB82; Mon, 26 Jul 2004 17:11:41 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 26 Jul 2004 17:10:11 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F27772DF5A for ; Mon, 26 Jul 2004 17:10:11 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31684-29 for ; Mon, 26 Jul 2004 17:10:10 -0500 (EST) Received: from MXR-3.estpak.ee (ld3.estpak.ee [194.126.101.102]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BAA2072D7DD for ; Mon, 26 Jul 2004 17:10:10 -0500 (EST) Received: from porgand (213-35-251-214-dsl.kvm.estpak.ee [213.35.251.214]) by MXR-3.estpak.ee (Postfix) with SMTP id C519B490D3 for ; Tue, 27 Jul 2004 01:37:00 +0300 (EEST) Message-ID: <398901c47361$10af4380$0a879fd9@porgand> From: =?windows-1257?Q?Tanel_P=F5der?= To: References: Subject: Re: Move table online and update the indexes at the same time. Date: Tue, 27 Jul 2004 01:36:59 +0300 MIME-Version: 1.0 Content-type: text/plain; charset=windows-1257 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new-20030616-p7 (Debian) at neti.ee X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 6088 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org > 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@freelists.org 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 -----------------------------------------------------------------