SQL> 
SQL> -- "create materialized view" right needs to be granted
SQL> 
SQL> conn scott/tiger@athena
Connected.
SQL> 
SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_       
------------------------------ ------------------------------ --- --- ---       
SCOTT                          CONNECT                        NO  YES NO        
SCOTT                          PLUSTRACE                      NO  YES NO        
SCOTT                          RESOURCE                       NO  YES NO        

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM     
------------------------------ ---------------------------------------- ---     
SCOTT                          CREATE ANY OUTLINE                       NO      
SCOTT                          UNLIMITED TABLESPACE                     NO      

SQL> 
SQL> CREATE MATERIALIZED VIEW emp_a_mv
  2  TABLESPACE users
  3  ON PREBUILT TABLE
  4  REFRESH complete
  5  as select * from emp;
REFRESH complete
*
ERROR at line 4:
ORA-00922: missing or invalid option 


SQL> 
SQL> CREATE MATERIALIZED VIEW emp_a_mv
  2  ON PREBUILT TABLE
  3  REFRESH complete
  4  as select * from emp;
as select * from emp
                 *
ERROR at line 4:
ORA-01031: insufficient privileges 


SQL> 
SQL> conn sys/athena@athena as sysdba
Connected.
SQL> grant create materialized view to scott;

Grant succeeded.

SQL> 
SQL> conn scott/tiger@athena
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM     
------------------------------ ---------------------------------------- ---     
SCOTT                          CREATE SNAPSHOT                          NO      
SCOTT                          CREATE ANY OUTLINE                       NO      
SCOTT                          UNLIMITED TABLESPACE                     NO      

SQL> 
SQL> CREATE MATERIALIZED VIEW emp_a_mv
  2  ON PREBUILT TABLE
  3  REFRESH complete
  4  as select * from emp;
as select * from emp
                 *
ERROR at line 4:
ORA-12059: prebuilt table "SCOTT"."EMP_A_MV" does not exist 


SQL> 
SQL> create table emp_a_mv as select * from emp;

Table created.

SQL> 
SQL> CREATE MATERIALIZED VIEW emp_a_mv
  2  ON PREBUILT TABLE
  3  REFRESH complete
  4  enable query rewrite
  5  as select * from emp;

Materialized view created.

SQL> 
SQL> drop materialized view emp_a_mv;

Materialized view dropped.

SQL> 
SQL> -- Can specify 'start with' and 'next' even when it is 'on demand'
SQL> CREATE MATERIALIZED VIEW emp_a_mv
  2  ON PREBUILT TABLE
  3  REFRESH complete on demand
  4  START WITH sysdate NEXT sysdate+2/24
  5  ENABLE QUERY REWRITE
  6  as select * from emp;

Materialized view created.

SQL> 
SQL> spool off