From oracle-l-bounce@freelists.org Mon Sep 5 14:45:14 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j85JjECr028098 for ; Mon, 5 Sep 2005 14:45:14 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j85JjBIP028088 for ; Mon, 5 Sep 2005 14:45:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4F1B01E97B6; Mon, 5 Sep 2005 14:45:08 -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 02924-05; Mon, 5 Sep 2005 14:45:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C0D9E1E984E; Mon, 5 Sep 2005 14:45:07 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Materialized Views - Overall Approach/Steps - for review Date: Tue, 6 Sep 2005 01:12:23 +0530 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Materialized Views - Overall Approach/Steps - for review thread-index: AcWxwUb+hdTXOMbwSw23h3sNSeCNBAAj2fLA From: "VIVEK_SHARMA" To: Cc: "Oracle-L" , X-OriginalArrivalTime: 05 Sep 2005 19:42:25.0150 (UTC) FILETIME=[F0C589E0:01C5B251] X-archive-position: 25017 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: VIVEK_SHARMA@infosys.com Precedence: normal Reply-To: VIVEK_SHARMA@infosys.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.8 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Thanks Jared for the review. Qs Any thumb rules / basic principles to estimate amount of Undo Space needed on Target/Slave Site? Qs Any best practices Docs/Links etc on the usage of MV? Our Production DB is a Hybrid Banking Application. There is a mix of OLTP Trans & Reports. DB size is 600 GB. Largest Table is 70 GB Will provide any info needed. ________________________________________ From: Jared Still [mailto:jkstill@gmail.com] Sent: Monday, September 05, 2005 7:57 AM To: VIVEK_SHARMA Subject: Re: Materialized Views - Overall Approach/Steps - for review Thanks Vivek, I modifiied my own procedure after reviewing this. Somehow I had determined that the target table needed a primary key in order to be used as 'prebuilt', but that was incorrect. After reviewing this I modified my procedures and saved several hours and 40 gig of rollback space. Jared On 9/2/05, VIVEK_SHARMA wrote: Jared,Tim, Folks   Following is our overall approach/Steps of transfer of DB from SUN to AIX using MV for your review.   At Source/Master DB:- ==================== scott@ORA9IR1> alter table emp add constraint emp_pk   2  primary key ( empno ); Table altered. (Since Unique indexes already PRE-Exist, above PK creation will be very Fast) scott@ORA9IR1> create materialized view log on emp; Materialized view log created. At Target / Slave DB:- ==================== ops$tkyte@ORA9IR2> create table emp as select * from emp@ora9ir1 where 1=0; Table created. ops$tkyte@ORA9IR2> create materialized view emp   2  on prebuilt table   3  refresh complete   4  as   5  select * from emp@ora9ir1; Materialized view created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> begin   2     dbms_refresh.make(   3        name             =>'group1',   4        list             =>'emp,dept',   5        next_date        =>sysdate,   6        interval         =>'sysdate+1/24',   7        implicit_destroy =>true);   8  end;   9  / PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> exec dbms_refresh.refresh( 'GROUP1' ); PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> alter materialized view emp refresh fast; Materialized view altered. At Source/Master DB:- ==================== ops$tkyte@ORA9IR2> @connect scott/tiger@ora9ir1.localdomain   scott@ORA9IR1 > update emp set ename = initcap(ename) where rownum = 1; 1 row updated. scott@ORA9IR1> commit; Commit complete. (Above done to simulate Production updates on Source DB)   At Target / Slave DB:- ==================== ops$tkyte@ORA9IR2> exec dbms_refresh.refresh( 'GROUP1' ); PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> drop materialized view emp; Materialized view dropped. ops$tkyte@ORA9IR2> select count(*) from emp;   COUNT(*) ----------         14 **************** CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l