LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon