I got a question from one of my clients that he has a query that he used to run it successfully on Oracle Database 10g,
but when he run the same query on Oracle Database 220.127.116.11, the query failed and the connection dropped with the following errors:
He confirmed many times that the same query with the same syntax is running fine against the same schema on 10g.
The problematic query is similar to:
I asked the client to send me the database alert.log file and I found the following error when we run the above query:
With some quick research on the above error, I found that it is a known bug in Oracle Database version 18.104.22.168:
This bug is fixed in 12.2 and is patched in 22.214.171.124
Also, there is a workaround which is to run the following statement before running the query:
ALTER SESSION SET “_optimizer_unnest_scalar_sq”=false;
Actually, this workaround fixed the query issue and it run successfully.
I asked my client to upgrade to a more stable version, 126.96.36.199 is a buggy version, both 188.8.131.52 & 184.108.40.206 are much more stable.