Oracle materialized view and ORA-01031 error
Fix ORA-01031 when creating a materialized view in another schema, and retrieve its DDL.
Creating a materialized view in another schema can fail with ORA-01031 when the user lacks the CREATE TABLE privilege. A materialized view stores its data in a table, so this privilege is needed.
Error
CREATE MATERIALIZED VIEW app_owner.app_mv AS SELECT * FROM dual;
ERROR at line 1:
ORA-01031: insufficient privileges
Solution
Grant the CREATE TABLE privilege directly to the schema owner:
alter session set current_schema = app_owner;
GRANT CREATE TABLE TO app_owner;
Verification
Check the current user and its system privileges, then create the materialized view again:
select user from dual;
select * from user_sys_privs;
CREATE MATERIALIZED VIEW app_owner.app_mv AS SELECT * FROM dual;
Materialized view created.
Retrieve the materialized view definition
Generate the DDL of a materialized view with DBMS_METADATA:
set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
spool recreate_sql
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','APP_MV','APP_OWNER') txt from dual;
spool off
set pages 9999
The query of an existing materialized view is also available in dba_mviews:
select query from dba_mviews
where mview_name = 'your materialized view';
References
- https://www.krenger.ch/blog/create-materialized-view-ora-01031/
- https://support.oracle.com/epmos/faces/DocContentDisplay?id=749112.1