I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle). This includes joining a subquery to 5 additional tables across three total schemas, and then unioning to another copy of the query against 3 different schemas. The account creating the view has DBA and CONNECT roles, and has been granted
GLOBAL QUERY REWRITE,
CREATE MATERIALIZED VIEW, and
CREATE TABLE system privileges. It also has explicit SELECT permissions on all of the tables in the query.
I can run the query itself without issue. However, when I add the setup for a materialized view:
CREATE MATERIALIZED VIEW <account_schema>.<materialized_view_name> BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS <SELECT...>
I get ORA-01031, or "insufficient privileges". What am I missing? Is there a way to query for a more detailed report of what's missing? It points to a specific location in the query (the table in the last join), but I don't see any difference between it and the other tables.