ORA-00904: “STRDEF”: invalid identifier

0
votes

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?

1
oracleoracle11gora-00904
And what is the query? – Wernfried Domscheit
Is your Production database the same (same version, same edition, same licensed extras) as the database you're complaining about? – APC
If your query invokes a user-defined function, for example, STRDEF may be referenced in that function - and the query fails at that point. – mathguy
You could also be querying a view and the underlying view has the function. You need to provide the query and the DDL for the objects being queried - at a minimum. – BriteSponge
There is one STRDEF in Oracle, it's part of the DBMS_XPLAN functionality for displaying Column Projection Information in the extended explain plan. It's not clear whether this is relevant to your scenario: you need to post more information before we can help you. – APC

1 Answers

0
votes

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.