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)
- 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;
8、We need replace the RNAME value of script (statement in red),using step 7 select result.
- begin
- DBMS_REFRESH.ADD(name => 'PRDADMC. R_PRDADMC_TKUDBX31_1',list => 'PRDADMC.PARAM_FLUX_SENDER',lax => 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'
