Hi,
We have some data warehouse loads which have been running for years without a problem.
However, at random areas we have seen on an intermittent basis the following ORACLE error
ORA-00904 <alias>,<column> invalid identifier occur in the last few months.
This can occur in random programs referencing the actual table but not the alias or the column concerned.
The error always refers to the same table and the same column and whilst the table concerned is referred to in the code, the column concerned is not and the table doesn't have the same alias.
The typical causes of this error don't look to apply here and when we run the code the next time so far has always ran successfully.
The alias reported in the sql concerned is not even used in the sql which reports the error.
When doing a search for any sql which does refer to that alias and column can see there are some other sqls.
My belief is that this could be related to shared pool potentially?
Intermittent ORA-904 (Doc ID 5260370.8)
Bug 2966778 - Intermittent ORA-904 errors during heavy load (Doc ID 2966778.8)
These refer to versions before one we are using which is 11.2.03. but wondered if anyone see similar behaviour before.
Curious as to why code can report an ORA-00904 referring to an alias which not even used in the code concerned.
One other thing though this would have been same for years have checked the table and the indexes and they show as VALID in user_tables and user_indexes but the dimension shows as INVALID in user_dimensions. though so do some other dimensions and they show no sqls reporting the ORA-00904 despite being used.
My belief is that since this has only started appearing this yea and this is when other separate code has direct reference to the alias concerned, it is these sqls which causing this error despite
I'm not familiar with shared pool but given referred to in these notes does that sound feasible?
Can other sqls (e.g sqla) cause error when running other sqls hitting same table but not referencing the table with that alias (sql b)
Has anyone seen anything similar?
Thanks