Create Mview step by step

After completing this document, you should be able to create new mview.

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

Operation Information:

Instance      Tetrix21_tkudpx05

Schema        PRDADMC

   Mview name    PARAM_FLUX_SENDER

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

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

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

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

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)

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

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

1. CREATE MATERIALIZED VIEW PRDADMC.PARAM_FLUX_SENDER TABLESPACE PRDADMC_SNAP_DATA 
2.  
3. BUILD IMMEDIATE USING INDEX TABLESPACE PRDADMC_SNAP_INDEX  REFRESH FORCE ON DEMAND AS 
4.  
5. SELECT * FROM "PRDADMC"."PARAM_FLUX_SENDER"@TETRIX21_TKUDBX31.DKTETRIX.NET; 

 7、Executing ‘select * from user_refresh;

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

1. begin 
2.  
3.   DBMS_REFRESH.ADD(name => 'PRDADMC. R_PRDADMC_TKUDBX31_1',list => 'PRDADMC.PARAM_FLUX_SENDER',lax => TRUE); 
4.  
5. end; 
6.  
7. / 

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

1. 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.

1. select mowner, r.snapsite, count(*) 
2.  
3. from   sys.slog$ s, sys.reg_snap$ r 
4.  
5. where  s.snapid=r.snapshot_id(+) 
6.  
7. group by mowner, r.snapsite 
8.  
9. 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.

1. grant select on PRDADMC.REF_SPECIFIC_CUSTOMER  to visu_prdadmc;  
2.  
3. create synonym visu_prdadmc.REF_SPECIFIC_CUSTOMER  for PRDADMC.REF_SPECIFIC_CUSTOMER; 
4.  
5. alter session set current_schema= visu_prdadmc; 
6.  
7. select * from dba_synonyms where table_name='REF_SPECIFIC_CUSTOMER'