ORA-03113 when running a specific query

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 12.1.0.1, 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 12.1.0.1:
Bug 17633803
This bug is fixed in 12.2 and is patched in 12.1.0.2

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, 12.1.0.1 is a buggy version, both 12.1.0.2 & 12.2.0.1 are much more stable.

Thanks
Ahmed

2 thoughts on “ORA-03113 when running a specific query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s