Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux
When a user tries to create a materialized view in a different schema, if the user of that schema does not have create table authority, the process of creating materialized view will result in an ORA-01031 error. In order to avoid this error, you must grant create table authorization to user/schema where materialized view is created.
In the following example, the “ahmet” user try to create a materialized view in the schema named “mehmet”.
|
1 2 3 4 5 6 |
SQL> conn ahmet/ahmet SQL> create materialized view mehmet.test_mv as select * from mehmet.test; create materialized view mehmet.test_mv as select * from mehmet.test * ERROR at line 1: ORA-01031: insufficient privileges |
When you grant CREATE TABLE privilege to “Mehmet” user, “Ahmet” user can successfully create materialized view in “Mehmet” schema.
|
1 2 3 4 5 6 |
SQL> conn / as sysdba SQL> grant create table to mehmet; SQL> conn ahmet/ahmet Connected. SQL> create materialized view mehmet.test_mv as select * from mehmet.test; Materialized view created. |
Author: dbtut
We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.
