The SQL tab of the Materialized View viewer has issues in SQL Developer; 20.2 makes that worse, but the good news is, at least some of the issues can be fixed (hacked) right away.
Remark: tested on DB 12.1, 12.2, and 19.5; SQL Developer 19.2 and 20.2
First, [issue #1] if you're not the MV owner, and you only have the SELECT privilege on someone else's MV, you're (definitely) out of luck: SQL Dev populates the lists of objects it can browse from SQL queries which perform inner joins to ALL_OBJECTS, and (oddly enough) that view does not list MVs in other schemas even though ALL_MVIEWS does. As a result, MVs in other schemas will just not appear. At this stage, you might want to be clever and use the SQLDEV:LINK trick in a custom query on ALL_MVIEWS; unfortunately, that link will not work either, because SQL Dev will not open the MV viewer without first running a query which performs an inner join to ALL_OBJECTS... (Therefore, the MV viewer will not open.)
Remarks:
You need the SELECT ANY TABLE system privilege for ALL_OBJECTS to list MVs in other schemas.
There must be good reasons why SQL Dev needs to query ALL_OBJECTS: in order to spare queries on the data dictionary, it caches a lot of objects' metadata in memory (e.g. column definitions) and it most likely needs the LAST_DDL_TIME timestamp from ALL_OBJECTS in order to ensure that cached metadata is current.
Which means you can't open the MV viewer in SQL Dev unless:
Either you are the MV owner
Or: you have the SELECT ANY TABLE system privilege--usually, because you've been granted the EXP_FULL_DATABASE role, or the DBA role.
Now, suppose you can open the MV viewer, and you reach the SQL tab in order to view the corresponding DDL statements. First, SQL Dev tries to generate these by calling the DBMS_METADATA package; if that fails, it falls back to its own internal DDL generator (and says so in a leading comment). Generally speaking that fallback may be necessary because, as stated in the documentation, in order to use DBMS_METADATA you need to be either the owner of the target object, or be SYS, or have the SELECT_CATALOG_ROLE role.
Which brings us to: [issue #2] SQL Dev 19.2 falls back to its internal DDL generator in cases where the DBMS_METADATA approach should work. Actually, it does this even when logged as SYS! The reason for this is a faulty SQL query (please see below).
And things gets worse in 20.2: [issue #3] the fallback to the internal DDL generator for MVs seems to be broken, i.e. we get no output anymore in cases where the internal DDL generator did work in 19.2--e.g. if having the EXP_FULL_DATABASE role and generating the DDL for a MV in someone else's schema.
Now the good news is, issue #2 can be fixed, without waiting for the next release, by fixing the above-mentioned faulty SQL query; this turns out to be a relatively easy hack--doing so will void your warranty, though. At least this works around issue #3, so long as you have the EXP_FULL_DATABASE or the DBA role.
We're kind-of lucky here: SQL Dev developers followed best practices in the MV viewer, namely:
Externalize SQL queries (away from the code) in separate resource files
Use bind-by-name bind variables for input values
So all that is needed is to change a single file: META-INF/ddl/oracle/MatViewSql.xml, inside the sqldeveloper/lib/dbtools-common.jar JAR file.
MatViewSql.xml declares 2 SQL queries, identified as "matview_ddl" and "script_ddl", which are very similar and should be fixed in the same fashion. Both queries are a UNION ALL of 3 single-row SELECTs:
select DBMS_METADATA.GET_XXX( ... ) from ...
Which return the DDL statement(s) for, respectively:
The MV itself
Indexes on the MV containing table, if any
The comment on the MV, if any
In the "matview_ddl" query, all 3 parts need to be fixed; in the "script_ddl' query, only the 2 last SELECTs need fixing.
In the first SELECT, the call to DBMS_METADATA.GET_DDL lacks :OWNER as its third argument. This is why the call raises an exception unless you are the owner of the object.
In the second SELECT, there are mistakes in the subquery in the FROM clause:
There should be a table alias, say "ai", for the ALL_INDEXES view
And: the subquery in the NOT IN predicate is wrong:
There should be a table alias, say "ac", for the ALL_CONSTRAINTS view
And: the column in the SELECT-list should be ac.index_name, not ac.constraint_name, because the PK index is not necessarily named the same as the PK constraint name
And: the following condition must be added to the WHERE clause: ac.owner = ai.table_owner
And: all columns must be qualified using the alias of the originating table, i.e. table_name = table_name (not kidding!) must be changed into ac.table_name = ai.table_name
The third SELECT must not be FROM dual, but rather FROM all_mview_comments where owner = :OWNER and mview_name = :NAME and comments is not null
(Because, yes, MVs created on pre-built tables do not necessarily have comments, yet they are still listed in the ALL_MVIEW_COMMENTS view; and DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT', ...) raises an exception if the comment is NULL--that's [issue #4] SQL Dev 20.2 fails to generate the DDL of a MV created on a pre-built table without a comment, even if connected as the owner of the MV. The above change on the third SELECT fixes that.)
I won't go into details here as to how to do the JAR file unpacking/repacking, there's plenty of that elsewhere on the internet.
(Hint: a JAR file is essentially a ZIP archive with class files + external resources in the META-INF directory-tree part... To make things properly and easily you may want to use the jar command of a JDK--and make sure you keep a backup of whichever file you change, just in case.)
The above fixes seem to work for both 19.2 and 20.2; the MatViewSql.xml file is the same in both releases (but beware: the dbtools-common.jar file is not!)
There are MVs in my project, and now I feel a lot happier with SQL Dev 20.2 enabled to generate DDL for them again. (Note that, in practice, you'll still need to have the EXP_FULL_DATABASE role in order to do this for MV's in other schemas.)
Credits, and Thanks!, to the SQL Developer team for following coding best practices--keeping SQL queries in external files, using bind-by-name--and for making it easy to view the SQL queries that the GUI sends to the database in the Statements - Log window. That made issues #2 and #4 easy to understand.
The remaining issues with the MV viewer are:
issue #1 (MVs in other schemas not showing in ALL_OBJECTS); this is definitely an issue with the ALL_OBJECTS view, not with SQL Developer
Issue #3 (the broken fallback to the internal DDL generator); it looks as if SQL Dev 20.2 failed to notice when DBMS_METADATA.GET_XXX raises an exception, which in turn disables the fallback to the internal DDL generator. Similar behaviour seems to happen with SYS views (e.g. ALL_OBJECTS) when the current container is not CDB$ROOT, so that may not be specific to materialized views.
My two cents; hope this helps!