Create Mview step by step

<b>After completing this document, you should be able to create new mview.</b>

1、  To facilitate the explanation , as a sample of creating mview at tetrix21_tkudpx05.

<b>Operation Information:</b>

Instance      Tetrix21_tkudpx05

Schema        PRDADMC

   Mview name    PARAM_FLUX_SENDER

First , running the PL/SQL software, connecting to tetrix21_tkudpx06 by dba account.

<a target="_blank" href="http://blog.51cto.com/attachment/201201/114650315.jpg"></a>

2、When Connected the instance, we run the statement ‘select * from dba_db_links’ to get the current db_link name.

<a target="_blank" href="http://blog.51cto.com/attachment/201201/114814657.jpg"></a>

3、We logon by PRDADMC.（First, you need know, what’s schema and mview will be created. ）

<a href="http://blog.51cto.com/attachment/201201/115144959.png" target="_blank"></a>

4、Executing ‘select * from PARAM_FLUX_SENDER;’ , to check whether or not the Mview exist.

<a href="http://blog.51cto.com/attachment/201201/115219239.png" target="_blank"></a>

5、Executing ‘select * from PARAM_FLUX_SENDER@ TETRIX21_TKUDBX31.DKTETRIX.NET;’ , to check the master Mview availability.(Red font need be replaced to step 2 db_link name)

<a href="http://blog.51cto.com/attachment/201201/115309549.png" target="_blank"></a>

6、If all above are OK, The following statement to create the mview.

.(Red font need be replaced to step 2 db_link name)

CREATE MATERIALIZED VIEW PRDADMC.PARAM_FLUX_SENDER TABLESPACE PRDADMC_SNAP_DATA 

BUILD IMMEDIATE USING INDEX TABLESPACE PRDADMC_SNAP_INDEX  REFRESH FORCE ON DEMAND AS 

SELECT * FROM "PRDADMC"."PARAM_FLUX_SENDER"@TETRIX21_TKUDBX31.DKTETRIX.NET; 

 7、Executing ‘select * from user_refresh;

<a href="http://blog.51cto.com/attachment/201201/115347330.png" target="_blank"></a>

8、We need replace the RNAME value of script (statement in red),using step 7 select result.

begin 

  DBMS_REFRESH.ADD(name =&gt; 'PRDADMC. R_PRDADMC_TKUDBX31_1',list =&gt; 'PRDADMC.PARAM_FLUX_SENDER',lax =&gt; TRUE); 

end; 

/ 

9、Creating the materialized view log. Attention, all the red font, you need to change.

CREATE MATERIALIZED VIEW LOG ON PRDADMC.PARAM_FLUX_SENDER TABLESPACE PRDADMC_SNAP_DATA; 

10、Please use this SQL to know how many slaves in this instance.

select mowner, r.snapsite, count(*) 

from   sys.slog$ s, sys.reg_snap$ r 

where  s.snapid=r.snapshot_id(+) 

group by mowner, r.snapsite 

order by 1 

11、When we know the slaves, we need to operate all the step at every instance.

12、We need grant right to VISU_xxxx account, and create synonym.

grant select on PRDADMC.REF_SPECIFIC_CUSTOMER  to visu_prdadmc;  

create synonym visu_prdadmc.REF_SPECIFIC_CUSTOMER  for PRDADMC.REF_SPECIFIC_CUSTOMER; 

alter session set current_schema= visu_prdadmc; 

select * from dba_synonyms where table_name='REF_SPECIFIC_CUSTOMER' 

本文轉自 hsbxxl 51CTO部落格，原文連結：http://blog.51cto.com/hsbxxl/763140，如需轉載請自行聯系原作者