Manage Distributed Materialized Views

Posted by:  Amit Kumar Srivastava - OCM11g

1.  From Oracle Documentation : Basic Materialized Views

2.  This objective requires us to know how to create a materialized in a distributed environment view, that is, create a separate MVIEW

# Create the DB LINK DB in TEST Database

# The first is to add the entry to the tnsnames.ora file

vi $ORACLE_HOME/network/admin/tnsnames.ora

# Add these lines

ORCL=

(DESCRIPTION=

  (ADDRESS=(PROTOCOL=tcp)(HOST=amit.example.com)(PORT=1521))

  (CONNECT_DATA=

     (SERVICE_NAME=ORCL)))

# Check that we have connectivity

tnsping ORCL

- Create the MVIEW LOG on the EMPLOYEES table

CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

- Create the DB LINK

CREATE PUBLIC DATABASE LINK ORCL CONNECT TO HR IDENTIFIED BY "hr" USING 'ORCL';

--- Tried connectivity

SELECT COUNT(*) FROM EMPLOYEES@ORCL;

- Create the EMP MVIEW pointing at table EMPLOYEES

CREATE MATERIALIZED VIEW EMP_MV REFRESH FAST AS

  SELECT * FROM EMPLOYEES@ORCL;

- Tried the Fast Refresh

                             

EXEC DBMS_MVIEW.REFRESH('EMP_MV','F');

3.       Materialized View Refresh Group: Create a group of materialized views and to make the data consistent between them.

- Create a MVIEW LOG in the DEPARTMENTS table (DATABASE ORCL)

CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS;

- Create a second materialized view in the database of ORCL

CREATE MATERIALIZED VIEW DEP_MV REFRESH FAST AS

  SELECT * FROM DEPARTMENTS@ORCL;

Create the refresh group

- Information can be found in these two views
--   · DBA_REFRESH

--   · DBA_REFRESH_CHILDREN

BEGIN

DBMS_REFRESH.MAKE (

  NAME => 'REFRESH_GROUP_TEST',

  LIST => 'EMP_MV,DEP_MV',

  NEXT_DATE => SYSDATE,

  INTERVAL => 'SYSDATE+1/1440',

  IMPLICIT_DESTROY => TRUE);

END;

/

- Cleaning environment

EXEC DBMS_REFRESH.DESTROY('REFRESH_GROUP_TEST');

DROP MATERIALIZED VIEW EMP_MV;

DROP MATERIALIZED VIEW DEP_MV;

DROP PUBLIC DATABASE LINK ORCL;

-- Deleting the MVIEW LOG

DROP MATERIALIZED VIEW LOG ON EMPLOYEES;

DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;