Materialized views Example

SET DEFINE OFF;
DROP SYNONYM APPSRO.EMP_SNAPSHOT;


DROP MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT;

CREATE MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT
TABLESPACE SYSTEM
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE SYSTEM
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP";

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO APPSRO;

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO PUBLIC;

CREATE SYNONYM APPSRO.EMP_SNAPSHOT FOR SCOTT.EMP_SNAPSHOT;