Materialized Views - Overall Approach/Steps - for review

["VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>]

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***

Other related posts:

Materialized Views - Overall Approach/Steps - for review

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

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