Materialized View REFRESH option

In this post I will be creating read only materialized view and giving different REFRESH option to get data updated in Master table into Materialized view.

Here, user SCOTT will be creating materialized view on HR.EMPLOYEES table. 

Manual Refresh using DBMS_MVIEW, DBMS_SNAPSHOT packages.

First, Grant needful privilege to SCOTT from SYS and HR schema.

---- As SYS ---

GRANT CREATE MATERIALIZED VIEW to SCOTT;

--- As HR ---

GRANT SELECT ON EMPLOYEES to SCOTT; 

-- As SCOTT --

SQL> CREATE MATERIALIZED VIEW scott.hremp
  2  AS SELECT * FROM HR.EMPLOYEES;

Materialized view created.

Querying Materialized view SCOTT.hremp

--- As Scott -- 

SQL> SELECT SALARY FROM HREMP
  2  WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8500

Now to refresh the materialized view:

Suppose you update one record in Employees Table:

--- As HR -- 

SQL> update employees set salary=8300 where employee_id =206;

1 row updated.

SQL> Commit;

SQL>  SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8300

-- As Scott

SQL> SELECT SALARY FROM HREMP WHERE EMPLOYEE_ID = 206;

    SALARY
----------

      8500

Now perform manual refresh using REFRESH method of DBMS_MVIEW package. 

SQL> exec DBMS_MVIEW.REFRESH('HREMP');
PL/SQL procedure successfully completed.

SQL>  SELECT SALARY FROM HREMP WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8300

Now we got refreshed data from HR.EMPLOYEES table into our materilized view SCOTT.HREMP.

Creating a Materialized View Refresh On commit on Master table:

We will be creating a materialized view again as above. Now in this case whenever commit on changes performed on master table (HR.EMPLOYEES) table is performed, our Materialized view (SCOTT.HREMP) should be refreshed quickly.

This is tricky and need some extra objects and privileges to come into picture.

We should have CREATE MATERIALIZED VIEW privilege, that can be verified as:

-- As SCOTT ---

SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE 


11 rows selected.

Now from HR schema we also need to create MATERIALIZED VIEW LOG table and GRANT permission on it to SCOTT.

--- AS HR -- 

First drop any already existing MView Log

SQL> DROP  MATERIALIZED VIEW LOG ON HR.EMPLOYEES;
Materialized view log dropped. 

Now create Materialized View Log:

SQL> CREATE MATERIALIZED VIEW LOG ON HR.EMPLOYEES;

Materialized view log created.

This will create a table: MLOG$_. Here MLOG$_EMPLOYEES will be created.

SQL> SELECT * FROM MLOG$_EMPLOYEES;
no rows selected

SQL> DESC MLOG$_EMPLOYEES;
 Name
------------------------------------
 EMPLOYEE_ID
 SNAPTIME$$
 DMLTYPE$$
 OLD_NEW$$
 CHANGE_VECTOR$$
 XID$$

Now we need to GRANT permissions to HR. 

First revoke any already present privilege:

--- AS HR -- 
SQL> REVOKE ALL ON EMPLOYEES FROM SCOTT;
Revoke succeeded.

Now GRANT the needful permissions to SCOTT.

SQL> GRANT SELECT ON EMPLOYEES TO SCOTT;
Grant succeeded.

SQL> GRANT SELECT ON MLOG$_EMPLOYEES TO SCOTT;
Grant succeeded.

Now lets try to create materialized view: 

-- AS SCOTT

SQL> CREATE MATERIALIZED VIEW  scott.hremp
  2          REFRESH FAST ON COMMIT
  3    AS  SELECT * FROM HR.EMPLOYEES;
  AS  SELECT * FROM HR.EMPLOYEES
                       *
ERROR at line 3:
ORA-01031: insufficient privileges

SCOTT need more permission on HR.EMPLOYEES table.

-- As HR

SQL> GRANT ALL ON HR.EMPLOYEES TO SCOTT;

Grant succeeded.

-- As SCOTT, Trying to create M View again

SQL> CREATE MATERIALIZED VIEW  SCOTT.HREMP
  2  REFRESH FAST ON COMMIT
  3  AS  SELECT * FROM HR.EMPLOYEES;
Materialized view created.

Fetching data from M View (SCOTT.HREMP) :

SQL> SELECT SALARY FROM SCOTT.HREMP
  2  WHERE EMPLOYEE_ID = 206;
    SALARY
----------
      8300

Perform changes to some records in employees table followed by commit.

-- As HR

SQL> update employees set salary=salary+200 where employee_id =206;
1 row updated.

SQL> Commit;

SQL> SELECT SALARY FROM HR.EMPLOYEES

  2  WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8500

-- As SCOTT -- 

SQL> SELECT SALARY FROM SCOTT.HREMP  WHERE EMPLOYEE_ID = 206;

    SALARY

----------

      8500

Immediately data get refreshed.

Now scheduling Materialized View Refresh