RE: Materialized Views - Overall Approach/Steps - for review
- From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>
- Date: Tue, 6 Sep 2005 01:12:23 +0530
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@xxxxxxxxx]
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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
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***
--
//www.freelists.org/webpage/oracle-l
Other related posts:
