RE: Materialized Views - Overall Approach/Steps

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 6 Sep 2005 01:12:23 +0530
Message-ID: <B5587533FCBD4344ADB8290B3EDDA12208BE94EE@kecmsg14.ad.infosys.com>

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_at_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 <VIVEK_SHARMA_at_infosys.com> 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_at_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_at_ORA9IR1> create materialized view log on emp;

Materialized view log created.

At Target / Slave DB:-

ops$tkyte_at_ORA9IR2> create table emp as select * from emp_at_ora9ir1 where 1=0;

Table created.

ops$tkyte_at_ORA9IR2> create materialized view emp

2 on prebuilt table
3 refresh complete

4 as
5 select * from emp_at_ora9ir1;

Materialized view created.

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_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_at_ORA9IR2> exec dbms_refresh.refresh( 'GROUP1' );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> alter materialized view emp refresh fast;

Materialized view altered.

At Source/Master DB:-

ops$tkyte_at_ORA9IR2> @connect
scott/tiger_at_ora9ir1.localdomain

scott_at_ORA9IR1
> update emp set ename = initcap(ename) where rownum = 1;

1 row updated.

scott_at_ORA9IR1> commit;

Commit complete.

(Above done to simulate Production updates on Source DB)

At Target / Slave DB:-

ops$tkyte_at_ORA9IR2> exec dbms_refresh.refresh( 'GROUP1' );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> drop materialized view emp;

Materialized view dropped.

ops$tkyte_at_ORA9IR2> select count(*) from emp;

COUNT(*)

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

Received on Mon Sep 05 2005 - 14:45:14 CDT

RE: Materialized Views - Overall Approach/Steps - for review