ORA-00904: "STRDEF": invalid identifier

Question

I'm getting this Error while executing the query in Oracle Database :

ORA-00904: "STRDEF": invalid identifier

1. Oracle Database Version is :

   Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

2. STRDEF is not an attribute in the query referenced anywhere in tables / views.

3. The DB was refreshed recently from Production.

Anyone seen this kind of issue before?

Answer 1

Issue was with the Oracle Session Level parameter “QUERY_REWRITE_ENABLED”. From Oracle docs it’s found that it’s used by Oracle to quickly answer the query using materialized views. For some reason it was enabled at the database level causing all the queries to be rewritten by Oracle behind the scenes. This problem was introduced by Oracle while creating the fix.

It was linked to the issue discussed in -

ORA-00904: “from$_subquery$_014”.“column_name”: invalid identifier

JFYI we were facing this issue too.