SQL & PL/SQL

Hi,

I am trying to create a commit on refresh materialized view so that whenever I perform DML followed by commit on Master table it should get quickly reflect on my Materialized view.

I have given SELECT on table privilege to user where this view will be created

On Master:

GRANT SELECT ON HR.EMPLOYEES TO SCOTT.

From Scott:

CREATE MATERIALIZED VIEW  scott.hremp

       REFRESH FAST ON COMMIT

AS

SELECT * FROM HR.EMPLOYEES;

ERROR at line 4:

ORA-01031: insufficient privileges

Then I Grant all on EMPLOYEES table to SCOTT.

GRANT ALL ON hr.EMPLOYEES TO SCOTT;

Then got following error while creating materilized view:

SQL> CREATE MATERIALIZED VIEW  scott.hremp

  2         REFRESH FAST ON COMMIT

  3  AS

  4  SELECT * FROM HR.EMPLOYEES;

SELECT * FROM HR.EMPLOYEES

                 *

ERROR at line 4:

ORA-23413: table "HR"."EMPLOYEES" does not have a materialized view log

I create materialized view log on Master:

SQL> CREATE MATERIALIZED VIEW LOG on HR.EMPLOYEES;

Materialized view log created.

Even after this I am not able to create materialized view and now getting Table not Exist error:

SQL> CREATE MATERIALIZED VIEW  scott.hremp

  2         REFRESH FAST ON COMMIT

  3  AS

  4  SELECT * FROM HR.EMPLOYEES;

SELECT * FROM HR.EMPLOYEES

                 *

ERROR at line 4:

ORA-12018: following error encountered during code generation for "SCOTT"."HREMP"

ORA-00942: table or view does not exist

Kindly guide me.

Also I want to know why I needed to GRANT ALL to HR for creating this materialized View.