Create materialized view started breaking in 19.13. 19.14 is affected as well.<\/p>\n
In contrast, the issue doesn’t appear in RUs until up to 19.12 and in 21.4.<\/p>\n
The test case:<\/p>\n
create table t1 (n1 number) ;\n\ncreate table t2 (n1 number, c1 varchar2(4000)) ;\n\ncreate table t3 (n1 number, n2 number);\n\ncreate table t4 (n1 number);\n\ncreate or replace force view v1 ( n1, c1, n2 )\nas\n with\n cte_in_view as\n (select n1 from t1 )\n select t2.n1, t2.c1, 1\n from t2 \n left join cte_in_view c on c.n1 = t2.n1 \n;\n\ncreate materialized view mv\nas\n with\n cte_in_view as\n (select v1.n1, v1.c1 \n from v1\n left join t3 on t3.n1 = v1.n2\n left join t4 on t3.n2 = t4.n1\n ),\n cte2 as\n (select n1, c1\n from cte_in_view\n)\n select 1 as n1\n from cte2 cte2_1\n left join \n cte2 cte2_2\n on cte2_1.c1 = cte2_2.c1\n left join \n ( with\n cte_recursive (n1) \n as\n (select 1 as n1 \n from dual\n union all \n select n1 + 1\n from cte_recursive\n where n1 <=5 \n )\n select n1\n from cte_recursive\n) v2 on v2.n1 = cte2_1.n1 ;\n<\/code><\/pre>\nThe error message is<\/p>\n
ORA-00904: \"C1\": invalid identifier<\/code><\/pre>\nThere’s nothing wrong with the identifier C1. If we replace<\/p>\n
create materialized view mv<\/code><\/pre>\nwith<\/p>\n
create table t<\/code><\/pre>\nthe command runs without errors.<\/p>\n
The following conditions seem to lead to the problem:<\/p>\n
\n- common table expressions (CTE) with the same name appear in different views<\/li>\n
- multiple layers of nested CTEs (like babushka dolls)<\/li>\n
- recursive subqueries<\/li>\n<\/ul>\n
You can try changing any of the conditions above as a workaround. For example, if you rename cte_in_view, the query will run without errors.<\/p>\n
The other options are:<\/p>\n
\n- falling back to 19.12<\/li>\n
- upgrading to 21c<\/li>\n<\/ul>\n
I have tried flipping the configurable bug fixes (“_FIX_CONTROL”) that appeared in 19.13, but to no avail.<\/p>\n
Update on January 28, 2022<\/h1>\n
Oracle Support filed the new bug:
\nBug 33797784 : MATERIALIZED VIEW FAILS WITH ORA-00904 ON 19C DATABASE<\/p>\n","protected":false},"excerpt":{"rendered":"
ORA-00904: invalid identifier when creating materialized view in 19.13 and 19.14. The problem is fixed in 21c. Continue Reading