Auto Refresh Materialized View
We can create automatic refresh Materialized view in Oracle 9i.
1. Parameter need to check
Optimizer_mode Choose, First_rows or All_rows
Job_queue_process 1
Job_queue_interval 3600 (Optional)
Query_rewrite_enabled true
Query_rewrite_integrity enforced
Compatible 8.1.5.0.0 or Greater
2. Sample SQL to create Automatic Materialized View
CREATE MATERIALIZED VIEW
empdep
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT empno,ename,dname,loc,e.ROWID emp_rowid,
d.ROWID dep_rowid
FROM EMP e, DEPT d
WHERE e.deptno=d.deptno;
3. May be when you try to create you will get ORA-1031 because of insufficient privileges
a. Grant global query rewrite to scott;
b. Grant create session to scott;
c. Grant create snapshot to scott;
d. Grant create table to scott;
e. Grant create view to scott;
4. After you granted again you may get ORA-23413 error because you don’t have Snapshot log on master tables
a. Create materialized view log on emp with rowid;
b. Create materialized view log on dept with rowid;
Since ON COMMIT is used once you changed some thing table and do commit will automatically update the View also.
