when attempting to create a materialized view for the same schema/user account that owns the table it throws an error "ORA-01031: insufficient privileges"
the user account ssp_test has these system privileges but still will not create the materialized view.
SQL> select substr(grantee,1,20) grantee,
2 substr(privilege,1,30) privilege
3 from dba_sys_privs
4 where grantee = 'SSP_TEST';
GRANTEE PRIVILEGE
-------------------- ------------------------------
SSP_TEST CREATE ANY MATERIALIZED VIEW
SSP_TEST CREATE MATERIALIZED VIEW
SSP_TEST CREATE SESSION
SQL>
SQL> create user ssp_test identified by "********"
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 profile default
6 account unlock;
User created.
SQL> grant create session to ssp_test;
Grant succeeded.
SQL> grant create materialized view to ssp_test;
Grant succeeded.
SQL> grant create any materialized view to ssp_test;
Grant succeeded.
SQL> create table ssp_test.test_tab1
2 (col1 varchar2(20), col2 varchar2(20));
Table created.
SQL> alter table ssp_test.test_tab1 add constraints TEST_TAB1_PK primary key (col1);
Table altered
SQL> CREATE MATERIALIZED VIEW LOG ON ssp_test.test_tab1 WITH PRIMARY KEY INCLUDING NEW VALUES;
Materialized view log created.
SQL> create materialized view ssp_test.test_tab1_mv
2 nologging
3 cache
4 build immediate
5 refresh fast on commit as
6 select * from ssp_test.test_tab1;
select * from ssp_test.test_tab1
*
ERROR at line 6:
ORA-01031: insufficient privileges
SQL>
please advise. i think i might be missing something.
thank you,
warren